CODEWITHSUNDEEP
sqlsql-server
user9762321
user9762321

Reputation: 93

Getting Running Totals Per Year in SQL Server

I'm trying to get Running Totals for a column that dispays totals by month. I would like it to reset when a new year begins.

SELECT DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0) AS Payout_Month,SUM(PRINCIPAL_AMT)
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 >= '07/01/2019'
GROUP BY DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0)
Order BY DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0)

Here's what I 'd like it to display

Payout_Month   Payout_Sum   RollingSum
11/1/2019         15           15
12/1/2019         22           37
1/1/2020          17           17
2/1/2020         12            19

Etc. How could I create a third column that does this? Or if I can't create a third column, even if i were to get rid of Payout_Sum and just have the Rolling_sum, that would work as well.

Upvotes: 0

Views: 26

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use window functions:

SELECT DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0) AS Payout_Month,
       SUM(PRINCIPAL_AMT),
       SUM(SUM(PRINCIPAL_AMT)) OVER (PARTITION BY YEAR(ENTRY_DATE) ORDER BY MIN(ENTRY_DATE)) as running_sum

Upvotes: 1

Singh
Singh

Reputation: 173

SELECT DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0) AS Payout_Month,
SUM(PRINCIPAL_AMT) Payout_Sum   
     ,RollingSum = SUM(PRINCIPAL_AMT) over (PARTITION BY DATEADD(MONTH, 
DATEDIFF(Month, 0, ENTRY_DATE), 0) order by DATEADD(MONTH, DATEDIFF(Month, 0, 
ENTRY_DATE), 0))
    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 >= '07/01/2019'
    GROUP BY DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0)
    Order BY DATEADD(MONTH, DATEDIFF(Month, 0, ENTRY_DATE), 0)

Upvotes: 1

Related Questions