CODEWITHSUNDEEP
sqlsql-servert-sql
afk
afk

Reputation: 43

Running totals with empty dates - SQL

I would like to calculate the running stock totals of items in a warehouse, but also include weeks in which there is no movement. Sample data looks like below.

Year  WeekNumber Category  Units in
2019    1            A       10
2019    1            B       20
2019    2            A       15
2019    2            B       10
2019    3            A       15

In week 3, there is no 'units in' for category B, but I would still like to show the running total for this category.

I've written the following code which works for category A but for week 3, it doesn't show category B running total. 

sum(units in) over
(partition by category order by year, weeknumber)

Expected results

Year  WeekNumber  Category  Running Total
2019     1           A           10
2019     1           B           20
2019     2           A           25
2019     2           B           30
2019     3           A           40
2019     3           B           30

Any suggestions?

Thanks afk

Upvotes: 0

Views: 169

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think you want a cross join to generate all results and then a left join to bring in data. Then use window functions:

select yw.year, yw.weeknumber, c.category, t.units_in,
       sum(t.units_in) over (partition by c.category order by yw.year, yw.weeknumber) as running_units_in
from (select distinct category from t) c cross join
     (select distinct year, weeknumber from t) yw left join
     t
     on t.category = c.category and t.year = yw.year and t.weeknumber = yw.weeknumber;

Note: You may have better sources of weeks and categories than using select distinct.

Upvotes: 1

Related Questions