Cumelative Query Result - SQL Server Forums

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources