SQL - Cumulative sum in sql, base on continuous dates

Question

Suppose I have a table with data as below:

SELECT *
FROM TestTable
ORDER BY deliver_date

deliver_date    quantity
2015-10-01  5.00
2015-10-02  3.00
2015-10-05  10.00
2015-10-07  8.00
2015-10-08  6.00

I know how to do the cumulative as below:

SELECT t1.deliver_date, SUM(t2.quantity) AS cumQTY 
FROM TestTable t1 
INNER JOIN TestTable t2 ON t2.deliver_date <= t1.deliver_date 
GROUP BY t1.deliver_date
ORDER BY t1.deliver_date

result:

deliver_date    cumQTY
2015-10-01  5.00
2015-10-02  8.00
2015-10-05  18.00
2015-10-07  26.00
2015-10-08  32.00

But, is it possible for me to get the result as below?

deliver_date    cumQTY
2015-10-01  5.00
2015-10-02  8.00
2015-10-03  8.00
2015-10-04  8.00
2015-10-05  18.00
2015-10-06  18.00
2015-10-07  26.00
2015-10-08  32.00

Means, the date must follow continuously. For example: I do not have 2015-10-03 data in my TestTable table, but the cumulative table must show the date 2015-10-03

Appreciate if someone can help on this. Thank you.

What version of sql-server are you using? – Felix Pamittan Oct 21 '15 at 03:08 — Felix Pamittan, Oct 21 '15 at 03:08

score 2 · Accepted Answer · edited May 23 '17 at 12:03

You can do this using a Tally Table:

SQL Fiddle

DECLARE @startDate  DATE,
        @endDate    DATE

SELECT
    @startDate  = MIN(deliver_date),
    @endDate    = MAX(deliver_date)
FROM TestTable

;WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(DATEDIFF(DAY, @startDate, @endDate) + 1)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
),
CteAllDates AS(
    SELECT
        deliver_date = DATEADD(DAY, t.N-1, @startDate),
        quantity = ISNULL(tt.quantity, 0)
    FROM Tally t
    LEFT JOIN TestTable tt
        ON DATEADD(DAY, N-1, @startDate) = tt.deliver_date
)
SELECT
    deliver_date,
    cumQty = SUM(quantity) OVER(ORDER BY deliver_date)
FROM CteAllDates

First, you want to generate all dates starting from the MIN(deliver_date) up to MAX(deliver_date). This is done using a tally table, the CTEs from E1(N) up to Tally(N).

Now that you have all the dates, do a LEFT JOIN on the original table, TestTable, to get the corresponding quantity, assigning 0 if there is no matching dates.

Lastly, to get the cumulative sum, you can use SUM(quantity) OVER(ORDER BY deliver_date).

For more explanation on tally table, see my answer here.

SQL - Cumulative sum in sql, base on continuous dates

1 Answers1