updating table with Running Total

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

General SQL Server Forums

New to SQL Server Programming

updating table with Running Total

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-28 : 09:32:47

Have a table where I need to update a field with a running total.

Table OrderRebate
I need to calculate a running total on ext_rebate for each order number. So I need the running total to reset to zero when the order number changes.

I've got as far with the code below but it never resets. Does a running total for every record.

DECLARE @runningtotal decimal(16, 6)
SET @runningtotal = 0
UPDATE OrderRebate
SET @runningtotal = RunningRebateAmt = @runningtotal + ext_rebate
WHERE ord_no = ord_no

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-28 : 09:44:09

[code]DECLARE @RunningTotal DECIMAL(16, 6),
@OrderNumber {INT | VARCHAR(20)}

UPDATE OrderRebate
SET @RunningTotal = CASE
WHEN @OrderNumber IS NULL THEN Ext_Rebate
WHEN Ord_No <> @OrderNumber THEN Ext_Rebate
ELSE @RunningTotal + Ext_Rebate
END,
RunningRebateAmt = @RunningTotal,
@OrderNumber = Ord_No[/code]

E 12°55'05.25"
N 56°04'39.16"

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-28 : 09:50:23

Is there an easy way to Round the running total?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-28 : 10:16:12

figured it out.

ELSE Round((@RunningTotal + Ext_Rebate),2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources