Populating a list of dates without a defined end date - SQL server -


i have list of accounts , cost changes every few days. in list have start date every time cost updates new one, no column end date. meaning, need populate list of dates when end date specific account , cost, should deduced start date of same account new cost.

more or less that:
account   start date  cost
 one           1/1/2016   100$
 two           1/1/2016   150$
 one           4/1/2016   200$
 two           3/1/2016   200$

and result need be:
account    date    cost
 one       1/1/2016  100$
 one       2/1/2016  100$
 one       3/1/2016  100$
 one       4/1/2016  200$
 two       1/1/2016  150$
 two       2/1/2016  150$
 two       3/1/2016  200$

for example, if cost changed in middle of month, sample data hold 2 records (one per each unique combination of account-start date-cost), while results hold 30 records cost each , every day of month (15 first cost , 15 second one). costs given, , no need calculate them (inserted manually).

note result contains more records because sample data shows start date , updated cost account, of date. while results show cost every day of month.

any ideas?

solution bit long.

i added date test purposes:

declare @t table(account varchar(10), startdate date, cost int)  insert @t  values ('one','1/1/2016',100),('two','1/1/2016',150), ('one','1/4/2016',200),('two','1/3/2016',200), ('two','1/6/2016',500) -- row  ;with cte ( select     row_number() on (partition account order startdate) rn,     *   @t ),n(n)as  (   select 1 from(values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))m(n) ), tally(n) -- tally limited 1000 days (   select row_number()over(order n.n) - 1 n,n a,n b ),grouped (   select     cte.account, cte.startdate, cte.cost, cte2.cost cost2, cte2.startdate enddate   cte   join cte cte2   on cte.account = cte2.account   , cte.rn = cte2.rn - 1 ) -- used distinct avoid overlapping dates select distinct   case when datediff(d, startdate,enddate) = n cost2 else cost end cost,   dateadd(d, n, startdate) startdate,   account grouped join tally on datediff(d, startdate,enddate) >= n 

result:

cost  startdate   account 100   2016-01-01  1 100   2016-01-02  1 100   2016-01-03  1 150   2016-01-01  2 150   2016-01-02  2 200   2016-01-03  2 200   2016-01-04  1 200   2016-01-04  2 200   2016-01-05  2 500   2016-01-06  2 

Comments

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

caching - How to check if a url path exists in the service worker cache -

Redirect to a HTTPS version using .htaccess -