RUNNING TOTAL AND COMPARISON - 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)

RUNNING TOTAL AND COMPARISON

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-11-19 : 22:01:52

Hi,

I have a problem with my current stored procedure that is using cursor. It's slow and causing timeout expired for it computes a very large volume of records. Thus, i need to change the query without using cursor.

Below is the sample data:
CREATE TABLE EmpLevy (EmpCode nvarchar(8), WDate date, Amount decimal, MaxAmt decimal)

INSERT INTO EmpLevy VALUES ('001','20070101',10,50)
INSERT INTO EmpLevy VALUES ('001','20070102',10,50)
INSERT INTO EmpLevy VALUES ('001','20070103',10,50)
INSERT INTO EmpLevy VALUES ('001','20070104',10,50)
INSERT INTO EmpLevy VALUES ('001','20070105',5,50)
INSERT INTO EmpLevy VALUES ('001','20070106',10,50)
INSERT INTO EmpLevy VALUES ('001','20070107',5,50)

INSERT INTO EmpLevy VALUES ('002','20070101',10,30)
INSERT INTO EmpLevy VALUES ('002','20070102',10,30)
INSERT INTO EmpLevy VALUES ('002','20070103',5,30)
INSERT INTO EmpLevy VALUES ('002','20070104',10,30)

Expected result:

EmpCode WDate Amount RunningTotal MaxAmount
001 20070101 10 10 50
001 20070102 10 20 50
001 20070103 10 30 50
001 20070104 10 40 50
001 20070105 5 45 50
001 20070107 5 50 50

002 20070101 10 10 30
002 20070102 10 20 30
002 20070103 5 25 30

Thanks for your attention.
I hope you guys can help to solve this.

Cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 22:08:36

What happen to this 2 records ?


INSERT INTO EmpLevy VALUES ('001','20070106',10,50)
INSERT INTO EmpLevy VALUES ('002','20070104',10,30)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 22:16:53

try this

SELECT	e.EmpCode, e.WDate, e.Amount, 
	RunningTotal = (SELECT SUM(Amount) FROM EmpLevy x WHERE x.EmpCode = e.EmpCode AND x.WDate <= e.WDate),
	e.MaxAmt
FROM	EmpLevy e
ORDER BY EmpCode, WDate

KH
[spoiler]Time is always against us[/spoiler]

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-11-19 : 22:26:24

Hi KH,

Thanks again.

Those two records shouldn't be included because if it will cause the running total more than its maximum amount paid per month.

I was able to produce the result of your query too, but I'm kind of thinking if someone can help me to generate the expected result (without those two records) directly rather than occupying another temp table to eliminate the unwanted records.

Cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 22:36:42

quote:
Those two records shouldn't be included because if it will cause the running total more than its maximum amount paid per month.

Aiyah, then should say so lar.

SELECT	*
FROM
(
	SELECT	e.EmpCode, e.WDate, e.Amount, 
		RunningTotal = (SELECT SUM(Amount) FROM EmpLevy x WHERE x.EmpCode = e.EmpCode AND x.WDate <= e.WDate),
		e.MaxAmt
	FROM	EmpLevy e
) a
WHERE	RunningTotal	<= MaxAmt
ORDER BY EmpCode, WDate

KH
[spoiler]Time is always against us[/spoiler]

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-11-20 : 00:51:49

haha..

thanks..

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources