SQL running total - add "missing" values

Question

SQL Server 2008 R2

Example data:

ownership  exact_opening_date
Type1       3/1/2002
Type1       1/4/2004
Owned       3/1/2002
Owned       3/31/2003
Owned       6/30/2004

I want to get a running total by year by ownership type but keep the running total going even when there isn't a value in that year:

ownership open_date run_total
Type 1    2002      1
Type 1    2003      1  <-- here's my trouble
Type 1    2004      2

I can get the running total, but am unsure how to include that running total when I don't actually have a value in that year.

Here's what I'm doing now:

WITH cte (
ownership
,open_date
,ct
)
AS (
    SELECT ownership
        ,year(exact_opening_date) AS open_date
        ,count(*) AS ct
    FROM studio_master
    GROUP BY ownership
        ,year(exact_opening_date)
    )
SELECT d1.ownership
    ,d1.open_date
    ,sum(d2.ct) AS run_total
FROM cte d1
LEFT JOIN cte d2 ON d1.open_date >= d2.open_date
    AND d1.ownership = d2.ownership
GROUP BY d1.ownership
    ,d1.open_date
    ,d1.ct
ORDER BY d1.ownership
    ,d1.open_date

How do I get those "missing" running total years in there?

score 4 · Accepted Answer · edited May 23 '17 at 12:11

4

You can use a list of years to join against.

You can use "Itzik's cross-joined CTE method" from the accpted answer to this question as your source for the list of years: SQL, Auxiliary table of numbers

edited May 23 '17 at 12:11

Community

1
1

answered Mar 16 '13 at 23:28

MattW

4,480
1
12
11

Thank you. I can't say I completely understand that yet, but I did get it to work. – duffn Mar 17 '13 at 01:03
`E00` is a logical table with two rows; then each successive CTE cross joins the previous one against itself which squares the number of rows, so `E02` has 4, `E04` has 16, `E08` has 256 etc. At the end it takes the `ROW_NUMBER()` over all the rows to get all the natural numbers up to 4 billion. The query optimizer is pretty good about knowing where it can stop generating logical rows, but it's _best_ at doing it right when you take `TOP n`. I haven't found myself needing a tally table often, but when I do, I set up a view using this method. – MattW Mar 17 '13 at 14:56

SQL running total - add "missing" values

1 Answers1