Rolling sum based on date range in sql

Question

Looking for a performance effective SQL code to calculate Rolling Sum based on DATE Sum. My data looks like:

+----+-----------+----+
| ID |   Date    | Qt |
+----+-----------+----+
|  1 | 1/12/2009 |  2 |
|  2 | 1/13/2009 |  3 |
|  3 | 1/14/2009 | 10 |
|  4 | 1/15/2009 |  2 |
|  5 | 1/16/2009 |  3 |
|  6 | 1/17/2009 |  7 |
|  7 | 1/18/2009 |  5 |
|  8 | 1/19/2009 |  4 |
|  9 | 1/20/2009 |  2 |
| 10 | 1/21/2009 |  3 |
| 11 | 1/22/2009 | 10 |
| 12 | 1/23/2009 |  2 |
| 13 | 1/24/2009 |  3 |
| 14 | 1/25/2009 |  7 |
| 15 | 1/26/2009 |  5 |
| 16 | 1/27/2009 |  4 |
| 17 | 1/28/2009 |  1 |
| 18 | 1/29/2009 |  0 |
| 19 | 1/30/2009 |  8 |
| 20 | 1/31/2009 |  9 |
+----+-----------+----+

Output should be like:

+----+-----------+----+-----------+
| ID |   Date    | Qt | Roll10Day |
+----+-----------+----+-----------+
|  1 | 1/12/2009 |  2 |         2 |
|  2 | 1/13/2009 |  3 |         5 |
|  3 | 1/14/2009 | 10 |        15 |
|  4 | 1/15/2009 |  2 |        17 |
|  5 | 1/16/2009 |  3 |        20 |
|  6 | 1/17/2009 |  7 |        27 |
|  7 | 1/18/2009 |  5 |        32 |
|  8 | 1/19/2009 |  4 |        36 |
|  9 | 1/20/2009 |  2 |        38 |
| 10 | 1/21/2009 |  3 |        41 |
| 11 | 1/22/2009 | 10 |        49 |
| 12 | 1/23/2009 |  2 |        48 |
| 13 | 1/24/2009 |  3 |        41 |
| 14 | 1/25/2009 |  7 |        46 |
| 15 | 1/26/2009 |  5 |        48 |
| 16 | 1/27/2009 |  4 |        45 |
| 17 | 1/28/2009 |  1 |        41 |
| 18 | 1/29/2009 |  0 |        37 |
| 19 | 1/30/2009 |  8 |        43 |
| 20 | 1/31/2009 |  9 |        49 |
+----+-----------+----+-----------+

Can we achieve it using a window function? I know I can get it be using inner query in SQL.

Solution 1

This would normally be handled using the windowing clause with sum():

select t.*,
       sum(qt) over (order by date rows between 9 preceding and current row)
from t;

I don't know if SAP Hana supports this syntax. SQL Server does.

Solution 2

Try this...

select
 ID
,Date
,Qt
,SUM(Qt) OVER (ORDER BY  ID  ROWS BETWEEN UNBOUNDED PRECEDING  AND  0  PRECEDING) AS 'Roll10Day'
from [TABLE NAME]

Comments

[Jay]

I was just thinking, if there is way to replace "BETWEEN 9 PRECEDING AND CURRENT ROW" by "BETWEEN [DATE - 9] and [DATE]"

[Jay]

I was just thinking if there is way to replace "BETWEEN 9 PRECEDING AND CURRENT ROW" by "BETWEEN [DATE - 9] and [DATE]"

[Gordon Linoff]

@Jay . . . I don't believe SQL Server supports that.