CODEWITHSUNDEEP
sqlsql-serversql-server-2008
kwknpui
kwknpui

Reputation: 19

rolling 3 months sum and row count from a irregularly-dated set on SQL

Is it possible to get the rolling 3 month sum(value) of each id?

Data:

+--------------------+---------------+----------------+---------------+
| tiq_transaction_id | id            | date           | value         |
+--------------------+---------------+----------------+---------------+
|           23980806 | a             | 2018-05-15     | 73614336.7168 |
|           24957628 | a             | 2018-09-28     | 47859499.2648 |
|           25403113 | a             | 2018-11-23     | 40708340.1377 |
|           25771232 | a             | 2019-01-07     | 15180519.2423 |
|           23455679 | b             | 2018-02-27     | 21032280.0296 |
|           24456317 | b             | 2018-07-25     | 8081544.0407  |
|           22963119 | c             | 2017-12-01     | 7570269.5457  |
|           22962927 | c             | 2017-12-04     | 8632491.8602  |
|           22962741 | c             | 2017-12-05     | 7029459.0614  |
|           22982978 | c             | 2017-12-06     | 5147303.2     |
|           22982751 | c             | 2017-12-07     | 7667280.7413  |
|           22982520 | c             | 2017-12-08     | 10090265.727  |
|           23007107 | c             | 2017-12-11     | 4930930.2813  |
+--------------------+---------------+----------------+---------------+

Upvotes: 1

Views: 900

Answers (2)

user330315
user330315

Reputation:

In standard SQL you can use window functions together with an interval definition:

select id, 
       date,
       value,
       sum(value) over (partition by id 
                        order by date 
                          range between '3 month' preceding and current row) as running_sum 
from the_table   
order by id, date;

Online example (using Postgres)

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query. (Applicable for MS SQL Server)

  1. Get your yyyyMM dateadd(month, datediff(month, 0, [date]), 0)
  2. use sum over your 3 months data based on yyyyMM
  3. then group by id
select id, dateadd(month, datediff(month, 0, [date]), 0) as yyyymm
        ,sum(sum([value])) over 
            (partition by id order by 
                dateadd(month, datediff(month, 0, [date]), 0) 
                    rows between 2 preceding and current row) as Rolling3Months
from test
group by id,  dateadd(month, datediff(month, 0, [date]), 0)
order by id, yyyymm

Try SQLFIDDLE

Upvotes: 0

Related Questions