CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
duffn
duffn

Reputation: 3760

SQL running total - add "missing" values

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?

Upvotes: 1

Views: 1349

Answers (1)

MattW
MattW

Reputation: 4628

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

Upvotes: 4

Related Questions