finding running year-wise total amount/ cumulative amount in SQL Server 2012

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 604
Sample data as follows:
CompanyID	Year	Profit 
1 2002 200000
1 2003 210000
1 2004 26000
2 2003 50000
2 2004 35000
2 2005 15000


The requiremnt is to calculate running total on the basis of continuous year ( RunningTotal_YearWise )
CompanyID	Year	Profit      RunningTotal_YearWise
1 2002 200000 200000
1 2003 210000 410000 ( ThisYearProfit+PreviousYearProfit)
1 2004 26000 436000 ( 26000+ 410000 = 436000)

2 2003 50000 50000
2 2004 35000 85000 ( 50000+35000 = 85000)
2 2005 15000 100000 ( 15000+85000 = 100000)



For this kind of calculations, we can make use of SUM() OVER() function in MSSQL 2012.
DECLARE @Companies TABLE 
( CompanyID int,
[Year] int,
Profit int
)
INSERT INTO @Companies (CompanyId,[Year],[Profit])
VALUES (1,2002,200000),(1,2003,210000),(1,2004,26000),
(2,2003,50000),(2,2004,35000),(2,2005,15000)

SELECT CompanyId,[Year],Profit,SUM(Profit) OVER (PARTITION BY CompanyID ORDER BY [Year]) AS RunningTotal
FROM @Companies

Comments or Responses

Login to post response