CODEWITHSUNDEEP
sql-servercommon-table-expression
Kyle Gobel
Kyle Gobel

Reputation: 5750

recursive sql query with sub running totals

I have a table something like this

create table installs (datetime date,installs int);
insert into installs (date, installs) 
values 
('10-01-2014', 2),
('10-02-2014', 4),
('10-03-2014', 1),
('10-04-2014', 10)

So our table data looks like this

Table Data

Given this table I would like a query that can produce the install count for days out.

So the result set I'm looking for would be

Result Set

So for example, looking at the records for day 10/2/2014.

On day_out=0 there were 4 installs. On the day after that (days_out=1), there are now 5 total installs (4 installs from 10/2, and 1 install from 10/3) On the next day after that (days_out=2), there were 15 total installs (5 from previous 2 days, and 10 new ones on the 4th)

I was doing this before in a larger query pretty easily just using an inner select to get the install counts (summing them on the spot), but, that no longer is cutting it...so the only solution i could think of was to create a table of the values, and then join this with the bigger query.

The only problem i'm having writing this query is I have no idea how to do it :).

If someone can think of a better title for the question I'll change it, was hard to name this when i'm not sure which direction to go with it.

Upvotes: 2

Views: 174

Answers (1)

jdawkins
jdawkins

Reputation: 180

This recursive CTE should do the trick.

WITH cte AS(
    SELECT date, 0 AS days_out, installs
        FROM installs
    UNION ALL
    SELECT cte.date, cte.days_out + 1, cte.installs + i.installs
        FROM cte
        INNER JOIN installs i ON cte.date = DATEADD(DAY, -1 * (cte.days_out + 1), i.date)
)
SELECT *
    FROM cte
    ORDER BY date, days_out

Upvotes: 5

Related Questions