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

Posted by Bandi under Sql Server category on | Points: 40 | Views : 1149
Sample data as follows:
`CompanyID	Year	Profit 1	2002	2000001	2003	2100001	2004	260002	2003	500002	2004	350002	2005	15000`

The requiremnt is to calculate running total on the basis of continuous year ( RunningTotal_YearWise )
`CompanyID	Year	Profit      RunningTotal_YearWise1	        2002	200000      200000 1	        2003	210000      410000  ( ThisYearProfit+PreviousYearProfit)1	        2004	26000       436000  ( 26000+ 410000 = 436000) 2	        2003	50000       500002	        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

Latest Code Snipptes