sql - Creating a table with a sequence of values without inserting each one manually -


i (think i) want create view or temporary table contains sequence. purpose of table provide values needed make panel data set. create sequence programmatically, using every value between 2 periods 0 , 365, gaps of 7 (say make weekly panel).
here how can done "manually", inserting each of cutoff days hand.

create table time_periods (day_cutoff int);  insert time_periods values (7);  insert time_periods values (14);  insert time_periods values (28);  insert time_periods values (35);  insert time_periods values (42);  

this table used (doing full cartesian join on underling table of billing_records contains ad hoc instances of when billing made.

select  buyer , seller , day_cutoff , sum(case when billing_day < day_cutoff        amount        else 0.0 end) cumulative_spend time_periods  left join billing_records  on 1 = 1  group buyer, seller, day_cutoff  

you can use generate_series:

select * generate_series(7, 42, 7); 

it documented here.

here 1 way write query:

select buyer, seller, day_cutoff,        sum(case when br.billing_day < day_cutoff amount else 0.0 end) cumulative_spend billing_records br cross join      generate_series(7, 42, 7) day_cutoff group buyer, seller, day_cutoff ; 

Comments

Popular posts from this blog

css - Which browser returns the correct result for getBoundingClientRect of an SVG element? -

gcc - Calling fftR4() in c from assembly -

Function that returns a formatted array in VBA -