Add Missing Dates in Running Total

Question

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.

Vamsi Prabhala · Accepted Answer · 2016-11-15 22:14:31Z

6

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

edited Nov 15, 2016 at 22:14

answered Nov 15, 2016 at 18:04

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3697498 Over a year ago

thank you for your response and that it something similar to what I had. I unfortunately had to make it a bit more complicated. Any help you could give is appreciated. Thanks

Vamsi Prabhala Over a year ago

@user3697498 .. i see your concern. see the edited version which should give you the expected result.

user3697498 Over a year ago

.. does this take into account the reverse cumulative sum? If you notice in my own codebit, i have ON FT1.Settle_Date < ft2.Settle_Date because I also dont want the day of to be summed.

Vamsi Prabhala Over a year ago

no..the code shown gives the result as per the output1 in question. which version of sql server are you using?

user3697498 Over a year ago

I am using 2012

|

Collectives™ on Stack Overflow

Add Missing Dates in Running Total

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related