Running Total Issue

[webidev]

Hi Everyone -

I am struck with one of script where i have to find the running totals from it. I am using SQL Server 2005.

SELECT
@UserID ID
,Event Event
,(CASE WHEN ShipDate < DATEADD(wk,-2,StDt)+1 THEN '2Weeks'
WHEN ShipDate < DATEADD(wk,-1,StDt)+1 THEN '1Week'
WHEN ShipDate < DATEADD(d,-1,StDt)+1 THEN 'Last Day'
ELSE 'Onsite'
END) Week
,COUNT(AttID) TotReg
FROM
TableA

Result from the above query will give (if i pass 1121 to @UserID):

ID Event Week TotReg
1121 EventA 2Weeks 45
1121 EventB 2Weeks 50
1121 EventA 1Week 65
1121 EventB 1Week 75
1121 EventA LastDay 25
1121 EventB LastDay 20
1121 EventA OnSite 25
1121 EventB Onsite 30



So from above result set i need a running total column where the result set will be as:

ID Event Week TotReg RunningTotal
1121 EventA 2Weeks 45 45
1121 EventB 2Weeks 50 50
1121 EventA 1Week 65 45+65 = 110
1121 EventB 1Week 75 50+75 = 125
1121 EventA LastDay 25 45+65+25 = 135
1121 EventB LastDay 20 50+75+20 = 145
1121 EventA OnSite 25 45+65+25+25 = 160
1121 EventB Onsite 30 50+75+20+30 = 175

Can anyone help me out how to get this running total.

 

[ousoonerjoe]

Here's an example of calculating Running Totals. I think if you adjust it to use a GROUP BY, it'll get you to what you are needing:

		SELECT atd.ApTranDetailId,
			atd.ApTransId,
			atd.Account,
			atd.Amount,
			atd.Descr,
			[RunTotal] = (SELECT SUM(Amount) FROM ApTransDetail 
						WHERE ApTransId = @ApTransId 
							AND ApTranDetailId <= atd.ApTranDetailId)
		FROM ApTransDetail atd
		WHERE atd.ApTransId = @ApTransId;

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------