CODEWITHSUNDEEP
sqlsql-server
user3697498
user3697498

Reputation: 115

Add Missing Dates in Running Total

If I have a table in SQL SERVER:

DATE          ITEM      Quantity_Received
1/1/2016      Hammer    20
1/3/2016      Hammer    50
1/5/2016      Hammer    100
...

And I want to output:

DATE         ITEM       Quantity_Running
1/1/2016     Hammer     20
1/2/2016     Hammer     20
1/3/2016     Hammer     70
1/4/2016     Hammer     70 
1/5/2016     Hammer     120

Where Quantity_Running is just a cumulative sum. I am confused on how I would add the missing dates to the output table that do not exist in the initial table. Note, I would need to do this for many items and would probably have a temp-table with the dates I want.

Thank you!

EDIT

And is there a way to do it such that you use an inner join?

 SELECT TD.Date, 
     FT1.Item,  
    SUM(FT2.QTY) AS Cumulative
FROM tempDates TD
LEFT JOIN FutureOrders FT1
ON TD.SETTLE_DATE = FT1.SETTLE_DATE
INNER JOIN FutureOrders FT2
ON FT1.Settle_Date < ft2.Settle_Date AND ft1.ITEM= ft2.ITEM
GROUP BY ft1.ITEM, ft1.Settle_Date

tempDates is a CTE that has the dates I want. Because I am then returning NULL values. And sorry, but to make it a bit more complicated I actually want:

    DATE         ITEM       Quantity_Running
1/1/2016     Hammer          150
1/2/2016     Hammer          100
1/3/2016     Hammer          100
1/4/2016     Hammer          0 
1/5/2016     Hammer          0

Thought I would be able to get the answer by myself based on my simpler question.

Upvotes: 1

Views: 2160

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Generate all the dates (for a specified range of dates) using a recursive cte. Thereafter left join on the generated dates cte and get the running sum for missing dates as well.

with dates_cte as 
(select cast('2016-01-01' as date) dt
 union all
 select dateadd(dd,1,dt) from dates_cte where dt < '2016-01-31' --change this date to the maximum date needed
 )
select dt,item,sum(quantity) over(partition by item order by dt) quantity_running 
from (select d.dt,i.item,
      coalesce(t.quantity_received,0) quantity
      from dates_cte d
      cross join (select distinct item from t) i
      left join t on t.dt=d.dt and i.item=t.item) x
order by 2,1

Edit: To get the reverse cumulative sum excluding the current row, use

with dates_cte as 
(select cast('2016-01-01' as date) dt
 union all
 select dateadd(dd,1,dt) from dates_cte where dt < '2016-01-31' --change this date to the maximum date needed
 )
select dt,item,
sum(quantity) over(partition by item order by dt desc rows between unbounded preceding and 1 preceding) quantity_running 
from (select d.dt,i.item,
      coalesce(t.quantity_received,0) quantity
      from dates_cte d
      cross join (select distinct item from t) i
      left join t on t.dt=d.dt and i.item=t.item) x
order by 2,1

Upvotes: 6

Related Questions