Running Total Issue - 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

General SQL Server Forums

New to SQL Server Programming

Running Total Issue

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-28 : 10:34:01

Using a trigger that when a record is inserted it calculates a running total after the insert. Code below:
CREATE TRIGGER [UPDATEOrderRebatewithRunningTotal] ON dbo.OrderRebate
AFTER INSERT
AS

DECLARE @RunningTotal DECIMAL(16, 6),
@OrderNumber VARCHAR(8)

UPDATE OrderRebate
SET @RunningTotal = CASE
WHEN @OrderNumber IS NULL THEN Ext_Rebate
WHEN Ord_No <> @OrderNumber THEN Ext_Rebate
ELSE round((@RunningTotal + Ext_Rebate),2)
END,
RunningRebateAmt = @RunningTotal,
@OrderNumber = Ord_No

My problem is that it is not calculating the last record.
Order is entered with 4 items I get the following.

Ord_no ext_rebate RunningRebateAmt
102 2.095 2.095
102 1.744 3.84
102 2.41 6.25
102 .0415 NULL

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-05-28 : 10:44:28

try this

CREATE TRIGGER [UPDATEOrderRebatewithRunningTotal] ON dbo.OrderRebate
AFTER INSERT
AS

DECLARE @RunningTotal DECIMAL(16, 6),
@OrderNumber VARCHAR(8)

SELECT @RunningTotal = ROUND(SUM(ISNULL(Ext_Rebate, 0)), 2)
FROM OrderRebate
WHERE Ord_No = @OrderNumber

UPDATE OrderRebate
SET RunningRebateAmt = @RunningTotal
WHERE Ord_No = @OrderNumber

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-28 : 10:51:32

That did not work. Now all values were NULL in RunningRebateAmt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources