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?

Community · Accepted Answer · 2017-05-23 12:11:05Z

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, 2017 at 12:11

CommunityBot

11 silver badge

answered Mar 16, 2013 at 23:28

MattW

4,6581 gold badge14 silver badges11 bronze badges

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

2 Comments

duffn Over a year ago

Thank you. I can't say I completely understand that yet, but I did get it to work.

MattW Over a year ago

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.

Collectives™ on Stack Overflow

SQL running total - add "missing" values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related