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
Post a Comment