user2173966 user2173966 - 1 month ago 11
SQL Question

How to calculate running total based on year in sql server 2012

How to calculate running value in sql server 2012 version.
Here is my sample data.

CREATE TABLE #RollingTotalsExample
(
[Date] DATE PRIMARY KEY
,[Value] INT
);

INSERT INTO #RollingTotalsExample
SELECT '2011-01-01',626
UNION ALL SELECT '2011-02-01',231 UNION ALL SELECT '2011-03-01',572
UNION ALL SELECT '2011-04-01',775
UNION ALL SELECT '2011-12-01',361 UNION ALL SELECT '2012-01-01',461
UNION ALL SELECT '2012-02-01',928 UNION ALL SELECT '2012-03-01',855
UNION ALL SELECT '2012-04-01',605
UNION ALL SELECT '2012-12-01',26;

SELECT * FROM #RollingTotalsExample;


Expected output:

Date Value Runing Value
1/01/2011 626 626
1/02/2011 231 857
1/03/2011 572 1429
1/04/2011 775 2204
1/12/2011 361 2565
1/01/2012 461 461
1/02/2012 928 1389
1/03/2012 855 2244
1/04/2012 605 2849
1/12/2012 26 2875


Running value should be calculated for year based.

Answer

Use window functions. Partition by Year(Date) will restart your running sum when year changes and order by date will ensure that sum will be calculated with date.

SELECT Date, Value, 
SUM(Value) OVER (PARTITION BY YEAR(DATE) ORDER BY DATE) AS RunningValue
FROM #RollingTotalsExample