Running totals with empty dates - SQL

Question

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

Edit your question and show the results you want.

Gordon Linoff
– Gordon Linoff

2019-08-29 14:16:13 +00:00
Commented Aug 29, 2019 at 14:16 — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2019 at 14:16
@GordonLinoff, expected results now shown in the question

afk
– afk

2019-08-30 07:55:36 +00:00
Commented Aug 30, 2019 at 7:55 — afk
– afk, Commented Aug 30, 2019 at 7:55

Gordon Linoff · Accepted Answer · 2019-08-29 14:18:03Z

1

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.

answered Aug 29, 2019 at 14:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Running totals with empty dates - SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related