Run-Time Running Total *(SOLVED)

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)

Run-Time Running Total *(SOLVED)

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-14 : 16:44:52

I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.

I want to avoid a Loop (i.e. cursor or a CTE).

Please help!



Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,	1 as BenefitInterestID,1701.00 as amount,	79.605 as InterestAmount ,	0.1000 as Interest,	0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',	2,	172.80,	7.92,	0.0500,	0
Union all
select '2008-12-01 00:00:00.000',	4,	0.00,	0.00,	0.0700,	0




UPDATE	s1
	SET	s1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)

	FROM	@Stage AS s1

select * from @Stage

My desired Results are


2006-12-01 00:00:00.000	1	1701.00	79.605	0.1000	0.0000
2007-12-01 00:00:00.000	2	172.80	7.92	0.0500	89.0303
2008-12-01 00:00:00.000	4	0.00	0.00	0.0700	143.5300

But I am geting


2006-12-01 00:00:00.000	1	1701.00	79.605	0.1000	0.0000
2007-12-01 00:00:00.000	2	172.80	7.92	0.0500	89.0303
2008-12-01 00:00:00.000	4	0.00	0.00	0.0700	137.2928

Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.

Please help!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-14 : 18:48:43

This works, but I'm not sure it it'll do anything more than solve your sample problem:

UPDATE 
	S
SET
	ai = Temp.AI
FROM
	@Stage AS S
INNER JOIN
	(
		SELECT 
			 S.BenefitInterestID,
			SUM(CASE WHEN T.ID = S.BenefitInterestID THEN T.MySum ELSE T.MySum * S.Interest END) AS AI
		FROM
			@Stage AS S
		INNER JOIN
			(
				SELECT 
					S.BenefitInterestID AS ID,
					S.Interest * (COALESCE(S1.Amount, $0.0) + COALESCE(S1.InterestAmount, $0.0) + COALESCE(S1.ai, $0.0)) AS MySum,
					S.Interest
				FROM 
					@Stage AS S
				LEFT OUTER JOIN
					@Stage AS S1
					ON S.StartDate > S1.StartDate
			) AS T
			ON T.ID <= S.BenefitInterestID
		GROUP BY
			 S.BenefitInterestID
	) AS Temp
	ON S.BenefitInterestID = Temp.BenefitInterestID

EDIT: Forgot the UPDATE part

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-14 : 19:25:03

Unfortuantly that still won't work.

I included a larger sample to illustrate better.


Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,	1 as BenefitInterestID,1701.00 as amount,	79.605 as InterestAmount ,	0.1000 as Interest,	0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',	2,	172.80,	7.92,	0.0500,	0
Union all
select '2008-12-01 00:00:00.000',	4,	0.00,	0.00,	0.0700,	0
Union all
select '2009-12-01 00:00:00.000',	5,	0.00,	0.00,	0.0900,	0
Union all
select '2010-12-01 00:00:00.000',	6,	0.00,	0.00,	0.0200,	0


UPDATE	s1
	SET	s1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)

	FROM	@Stage AS s1

select * from @Stage

Results I want


2006-12-01 00:00:00.000	1	1701.00	79.605	0.1000	0.0000
2007-12-01 00:00:00.000	2	172.80	7.92	0.0500	89.0303
2008-12-01 00:00:00.000	4	0.00	0.00	0.0700	143.5249
2009-12-01 00:00:00.000	5	0.00	0.00	0.0900	197.4492
2010-12-01 00:00:00.000	6	0.00	0.00	0.0200	47.82659

Actual results


2006-12-01 00:00:00.000	1	1701.00	79.605	0.1000	0.0000
2007-12-01 00:00:00.000	2	172.80	7.92	0.0500	89.0303
2008-12-01 00:00:00.000	4	0.00	0.00	0.0700	137.2928
2009-12-01 00:00:00.000	5	0.00	0.00	0.0900	176.5193
2010-12-01 00:00:00.000	6	0.00	0.00	0.0200	39.2265

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-14 : 19:43:52

Here is also some sample code that generates the correct Numbers using a while clause. As stated originally I really would like to not use this method.


Declare @Stage Table(RowID int,StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage(StartDate ,BenefitInterestID, Amount, InterestAmount, Interest , ai )
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,	1 as BenefitInterestID,1701.00 as amount,	79.605 as InterestAmount ,	0.1000 as Interest,	0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',	2,	172.80,	7.92,	0.0500,	0
Union all
select '2008-12-01 00:00:00.000',	4,	0.00,	0.00,	0.0700,	0
Union all
select '2009-12-01 00:00:00.000',	5,	0.00,	0.00,	0.0900,	0
Union all
select '2010-12-01 00:00:00.000',	6,	0.00,	0.00,	0.0200,	0



Update a
set a.RowID =aa.RowID2
From
@Stage a
Inner Join
(
Select Row_Number() over (order by a.StartDate) as RowID2,BenefitInterestID
from 
@Stage a
) aa
on a.BenefitInterestID = aa.BenefitInterestID

Declare @RowID int
set @RowID = 1
WHile exists (Select 1 from @Stage where RowID >= @RowID)
Begin

UPDATE	s1
	SET	s1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)

FROM	@Stage AS s1
	where s1.RowID = @RowID
set @RowID =  @RowID + 1
End

select * from @Stage

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 02:44:49

can you try like this:-

DECLARE @temp decimal(10,4)UPDATE	s1
	SET	@temp=s1.ai =  s1.Interest * (coalesce(@temp,0) + coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0))

	FROM	@Stage AS s1

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-15 : 10:43:36

That doesn't work either. Please referance the result for the 4 record in your query compared to the one in mine.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 11:21:46

sorry, but could you please show the math for the 3rd row of your desired output: 143.5249
I'm not following the logic.

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-15 : 11:45:30

The Math is that you need to add ALL the previously updated AI + InterestAmount + Amount columns
THen once you have that total you need to multiply that total by the Interest on the current record.

Please see my post with the loop clause at 05/14/2008 : 19:43:52 that shows a way to generate accurate numbers.

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 11:57:20

That is what I thought - I'm still loosing it at the 4th row but in the meantime here is what I'm doing:



declare @a money
       ,@ia money
       ,@ai decimal(10,4)
select @a = 0
       ,@ia = 0
       ,@ai = 0

update s set
       @ai = ai = s.Interest * (@ai + isNull((select sum(amount+InterestAmount) from @stage where BenefitInterestID < s.BenefitInterestID),0))
       ,@a = s.amount
       ,@ia = s.interestAmount
from   @stage s


select * from @Stage

output:
StartDate               BenefitInterestID Amount                InterestAmount        Interest                                ai
----------------------- ----------------- --------------------- --------------------- --------------------------------------- ---------------------------------------
2006-12-01 00:00:00.000 1                 1701.00               79.605                0.1000                                  0.0000
2007-12-01 00:00:00.000 2                 172.80                7.92                  0.0500                                  89.0303
2008-12-01 00:00:00.000 4                 0.00                  0.00                  0.0700                                  143.5249
2009-12-01 00:00:00.000 5                 0.00                  0.00                  0.0900                                  189.4365
2010-12-01 00:00:00.000 6                 0.00                  0.00                  0.0200                                  43.0152

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-15 : 12:52:24

I appreciate the help. It might just be one of those things where I am stuck needing to use a loop.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 13:53:42

this seems to work:


set nocount on
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage(StartDate ,BenefitInterestID, Amount, InterestAmount, Interest , ai )
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,	1 as BenefitInterestID,1701.00 as amount,	79.605 as InterestAmount ,	0.1000 as Interest,	0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',	2,	172.80,	7.92,	0.0500,	0
Union all
select '2008-12-01 00:00:00.000',	4,	0.00,	0.00,	0.0700,	0
Union all
select '2009-12-01 00:00:00.000',	5,	0.00,	0.00,	0.0900,	0
Union all
select '2010-12-01 00:00:00.000',	6,	0.00,	0.00,	0.0200,	0


declare @ai decimal(10,4)
       ,@aiTot decimal(10,4)
select @ai = 0
       ,@aiTot = 0

update s set
       @ai = s.interest * (@aiTot + isNull((select sum(d.amount+d.InterestAmount) from @stage d where d.BenefitInterestID < s.BenefitInterestID),0))
       ,ai = @ai
       ,@aiTot = @aiTot + @ai
from   @stage s

select * from @stage

output:
StartDate               BenefitInterestID Amount                InterestAmount        Interest                                ai
----------------------- ----------------- --------------------- --------------------- --------------------------------------- ----------
2006-12-01 00:00:00.000 1                 1701.00               79.605                0.1000                                  0.0000
2007-12-01 00:00:00.000 2                 172.80                7.92                  0.0500                                  89.0303
2008-12-01 00:00:00.000 4                 0.00                  0.00                  0.0700                                  143.5249
2009-12-01 00:00:00.000 5                 0.00                  0.00                  0.0900                                  197.4492
2010-12-01 00:00:00.000 6                 0.00                  0.00                  0.0200                                  47.8266

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 14:18:26

This version should be much better because it eliminates the subquery.
This technique depends on the updates happening in order of some sequence so it may require a clustered index.
technique being a running total with "update table set variable=Column=Expression"


select * into #stage from @stage
alter table #stage add primary key clustered (benefitInterestID)

declare @ai decimal(10,4)
       ,@aiTot decimal(10,4)
       ,@a money
       ,@ia money

update s set
       @ai = ai = s.interest * (isNull(@aiTot,0) + @a + @ia)
       ,@a = isNull(@a, 0) + s.amount
       ,@ia = isNull(@ia, 0) + s.interestamount
       ,@aiTot = isNull(@aiTot, 0) + @ai
from   #stage s

select * from #stage
drop table #stage

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-15 : 14:50:25

Works nicley.. Well done!

The final query using a dummy set of 500 records compared to the while loop I posted

Loop Query Execution time - 1913.5
Your Query Execution Time - 229.4

roughly 1/8 of the time!

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 16:00:42

You're welcome!

I suggest that before your final deployment you run a complete set of rows (both ways) and compare the new values to the process you know works.
Because as I said, this relies on the updates occurring in the proper sequence.

I've always shy'd away from that technique because other than creating a clustered index you really don't have control the order of the updates.
Over the long haul I don't think it is something you can count on being consistant - especially as new versions of sql server come out

EDIT:
put some hard returns in so we don't have to scroll

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-15 : 16:20:48

Didn't think of that..

I'm glad you pointed that out too, because it would've been a killer to debug later on if this ever caused a error.

U feel that there is a possibility of MS changing how a clustered index is referanced when updating with a future realese of sql??

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-15 : 19:56:51

Not so much that MS will change the nature and usage of a clustered index but rather they may
make "improvements" to the execution plans. Maybe they will perform the updates starting in the
middle of the table and work out to both ends - who knows :)

Seriously I don't think you'll have a problem. This is the way it has worked for a long time.
Technically speaking, you shouldn't assume the order updates will happen.
However I've never seen anyone complain with the following conditions:

1) you're updating a single table (no complex joins)
2) the table has a clustered index
3) you include an "anchor" SET @var=col along with the @var=col=exp.

I'd love to hear from anyone with experience about this if they agree or disagree...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources