Stored Proc - 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 2000 Forums

SQL Server Development (2000)

Stored Proc

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-22 : 07:15:28

Ash writes "Hi, I am trying to do some computation based on the following table,and I am using cursor but little lost to go forward.

inv_nbr col_no day amt_1 amt_2 amt_3 total
1002 1 1 20.00 30.00 40.00 ?
1002 1 2 0 0 0 ?
1002 1 3 10.00 -6.00 0
1002 1 4 0 0 0
. 1 .
. 1 . 5.0 45.00 3.00
. 1 . 0 0 0
. 1 . 0 0 0
1002 1 30 0 0 0
1002 2 1 10.00 6.0 0
1002 2 2 3.0 0 15.0
. 2 . 0 0 0
. 2 . 0 0 0
. 2 30 4.0 4.00 35.00

I need to add amt_1+amt_2+amt_3 together for each day eventhough
col_no is different, i.e, for day 1, col_no 1 and col_no 2 should add together and put the result in the total column. Basically, for two sets of col_no , only the total should be one set which is just 30 days, hope I could make this clear. Please let me know if you need more clarification. Quick response would be appreciated. TIA"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-22 : 13:27:04

a version of this should give you some ideas...


SELECT inv_nbr, col_no, day, amt_1, amt_2, amt_3, (amt_1+amt_2+amt_3)+COALESCE((SELECT SUM(amt_1+amt_2+amt_3) 
	                      			  FROM MyTable b 
	                      			  WHERE b.v_nbr = a.v_nbr and a.day < b.day),0)
	                         AS total
FROM MyTable a
ORDER BY day

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources