Rolling Sum for Last 12 Months in SQL

Question

I'm trying to get the rolling sum for the past 12 months (Oct 2019-Sept 2020, etc.)> So far, I figured out to get the current year total (which I also want), however, I'm stuck on a legit 12 month rolling sum.

    SELECT DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0) AS Payout_Month, SUM(PRINCIPAL_AMT) Payout_amt, 
SUM(SUM(PRINCIPAL_AMT)) OVER (PARTITION BY YEAR(ENTRY_DATE) ORDER BY MIN(ENTRY_DATE)) as YearRollingSum,
SUM(SUM(PRINCIPAL_AMT)) OVER (PARTITION BY Year(ENTRY_DATE)
                  ORDER BY MIN(ENTRY_DATE)
                  ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING
                 )  AS TwelveMonthRollingSum
FROM ACCOUNTHISTORY
WHERE LEFT(TOKEN_STRING, 4) LIKE '%Py%'
AND FOCUS_TELLER_ID = 6056
AND PRINCIPAL_AMT > 0 AND PRINCIPAL_AMT < 25
AND ENTRY_DATE >= '01/01/2019'
GROUP BY DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0), YEAR(ENTRY_DATE) 
Order BY DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0)

here's what my current output looks like

Payout_Month            Payout_amt  YearRollingSum  TwelveMonthRollingSum
2019-01-01 00:00:00.000 5696.50     5696.50            NULL
2019-02-01 00:00:00.000 11205.60    16902.10          5696.50
2019-03-01 00:00:00.000 23341.50    40243.60          16902.10
2019-04-01 00:00:00.000 25592.80    65836.40          40243.60
2019-05-01 00:00:00.000 28148.30    93984.70          65836.40
2019-06-01 00:00:00.000 27190.90    121175.60         93984.70
2019-07-01 00:00:00.000 25079.80    146255.40         121175.60
2019-08-01 00:00:00.000 30206.90    176462.30         146255.40
2019-09-01 00:00:00.000 28000.80    204463.10         176462.30
2019-10-01 00:00:00.000 29076.60    233539.70         204463.10
2019-11-01 00:00:00.000 29001.30    262541.00         233539.70
2019-12-01 00:00:00.000 28366.00    290907.00         262541.00
2020-01-01 00:00:00.000 32062.40    32062.40          NULL
2020-02-01 00:00:00.000 28526.70    60589.10          32062.40
2020-03-01 00:00:00.000 29056.50    89645.60          60589.10
2020-04-01 00:00:00.000 28016.00    117661.60         89645.60
2020-05-01 00:00:00.000 25173.30    142834.90         117661.60
2020-06-01 00:00:00.000 27646.10    170481.00         142834.90
2020-07-01 00:00:00.000 36083.70    206564.70         170481.00
2020-08-01 00:00:00.000 34872.20    241436.90         206564.70
2020-09-01 00:00:00.000 35727.10    277164.00         241436.90
2020-10-01 00:00:00.000 34030.80    311194.80          277164.00

AS you can see, it resets at the beginning of the year for the last column. Any ideas?

GMB GMB · Accepted Answer · 2020-10-29T00:42:23

Basically, you want to remove the partition by clause from the rolling 12 month sum. I would also suggest a few optimizations to the query:

select 
    x.payout_month, 
    sum(ah.principal_amt) payout_amt, 
    sum(sum(ah.principal_amt)) over (
        partition by year(x.payout_month) 
        order by x.payout_month
    ) as yearrollingsum,
    sum(sum(ah.principal_amt)) over (
        order by x.payout_month
        rows between 12 preceding and 1 preceding
    )  as twelvemonthrollingsum
from accounthistory ah
cross apply (values (datefromparts(year(ah.entry_date), month(entry_date), 1))) x(ah.payout_month)
where 
    left(ah.token_string, 4) like '%py%'
    and ah.focus_teller_id = 6056
    and ah.principal_amt > 0 and principal_amt < 25
    and ah.entry_date >= '20190101'
group by x.payout_month
order by x.payout_month

The main change is that the payout_month is computed only once, in a lateral join, using datefromparts(). You can then use it all over the query, and consistently in the order by clauses of the window functions.

Note that your strategy will fail to produce a proper results if you ever have a month without any sale (the rows clause of the window function will spread over the preceding month, which is not what you want). If that's something that may happen, then an alternative is a subquery, or another lateral join.

Rolling Sum for Last 12 Months in SQL

1 Answers