Need help with this SQL problem.

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)

Need help with this SQL problem.

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-11-27 : 08:11:21

i have a tables with 2 columns like this

table 1

row# __ A _____ B
1 _____ 1 _____ 5
2 _____ 6 _____ 7
3 _____ 13 ____ 2
4 _____ ? _____ 1
5 _____ ? _____ 6
6 _____ ? _____ 8

i need to calculate the value of the question mark which is 13+2=15 ... therefore the value in row#4col#1 is dependent on the previous rows value i.e row#4col#1=row#3col#1+row#2col#2 ..... then i want to continue through the table and calculate all the values when "?"'s are mentioned.

i think cursors would be the way to go please can you give me an example of how to accomplish this is TSQL ... I am using SQL 2000

thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-27 : 08:41:41

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 08:47:47

quote:
Originally posted by dpais

i have a tables with 2 columns like this

table 1

row# __ A _____ B
1 _____ 1 _____ 5
2 _____ 6 _____ 7
3 _____ 13 ____ 2
4 _____ ? _____ 1
5 _____ ? _____ 6
6 _____ ? _____ 8

i need to calculate the value of the question mark which is 13+2=15 ... therefore the value in row#4col#1 is dependent on the previous rows value i.e row#4col#1=row#3col#1+row#2col#2 ..... then i want to continue through the table and calculate all the values when "?"'s are mentioned.

i think cursors would be the way to go please can you give me an example of how to accomplish this is TSQL ... I am using SQL 2000

thanks in advance.

Where do you want to show data?
If you use Reports, make use of its Running Total feature
or refer http://sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18286.aspx

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 08:52:54

[code]DECLARE @table1 TABLE
(
row_no int,
a int,
b int
)
INSERT INTO @table1
SELECT 1, 1, 5 UNION ALL
SELECT 2, 6, 7 UNION ALL
SELECT 3, 13, 2 UNION ALL
SELECT 4, NULL,1 UNION ALL
SELECT 5, NULL,6 UNION ALL
SELECT 6, NULL,8

SELECT t1.row_no,
a = SUM(coalesce(t2.b, t1.a) + CASE WHEN t2.row_no = 1 THEN t2.a ELSE 0 END),
t1.b
FROM @table1 t1 left JOIN @table1 t2
ON t1.row_no > t2.row_no
GROUP BY t1.row_no, t1.b
ORDER BY t1.row_no

/*
row_no a b
----------- ----------- -----------
1 1 5
2 6 7
3 13 2
4 15 1
5 16 6
6 22 8

(6 row(s) affected)
*/[/code]

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

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-11-27 : 09:47:28

GREAT khtan - THANK YOU -

follow up question -

the row_id column that you have included in your table was used in my example for illustration purposes only - i will include an identity column instead - that should not change the logic of the code right ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-28 : 01:14:59

Yes. It does. You will need to find the row_no of the 1st row which may not necessary be 1

SELECT	t1.row_no, 
	a = SUM(coalesce(t2.b, t1.a) + CASE WHEN t2.row_no = 1 THEN t2.a ELSE 0 END),
	t1.b
FROM	@table1 t1 left JOIN @table1 t2
	ON	t1.row_no	> t2.row_no
GROUP BY t1.row_no, t1.b
ORDER BY t1.row_no

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources