Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Cumelative Query Result

Author  Topic 

sanjay5219
Posting Yak Master

240 Posts

Posted - 2014-07-05 : 11:52:25
Hi All,

I have a situation where I have to calculate result as Cumulative.

Please suggest how to calculate CUM based on Forumla column


Month Manpower Cum Formula
Apr '13 349.5 349.5 =SUM(Apr)
May '13 383.5 733 =SUM(Apr+May)
Jun '13 412.5 1145.5 =SUM(Apr+May+Jun)
Jul '13 417 1562.5 =SUM(Apr+May+Jun+July)
Aug '13 0
Sep'13 0
Oct'13 0
Nov'13 0
Dec'13 0
Jan '14 0
Feb '14 0
Mar '14 0

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-14 : 07:53:28
Are you looking for something along the lines of this?


CREATE TABLE #T
(
ID int IDENTITY(1,1),
MonthName Varchar(10),
Amount money
)


INSERT INTO #T
VALUES('April',349.5 ),('May ',383.5 ),('June',412.5 ),('July',417)


;With MyTotal
AS
( SELECT ID, MonthName,Amount ,Amount + 0 Cumulative
FROM #T
WHERE ID = 1
UNION ALL
SELECT T.ID, T.MonthName,t.Amount ,t.Amount + Mt.Cumulative
FROM MyTotal MT
INNER JOIN #T T ON MT.ID = T.ID - 1
)


SELECT * FROM
MyTotal
Go to Top of Page
   

- Advertisement -