update a table - 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

update a table

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 13:40:23

table1
id dd cumulativeincome
1 0 0
1 1 10
1 2
1 3 50
2 0 20
2 1
2 2 40
2 3 42
2 4
2 5
2 6 80
3--------

I want it to become

table1
id dd cumulativeincome
1 0 0
1 1 10
1 2 10
1 3 50
2 0 20
2 1 20
2 2 40
2 3 42
2 4 42
2 5 42
2 6 80
3--------

for exampe for id 1, day 2 does not have income. I just want to take the cumulative income from day1 as day2 's cumulative income.
How can i do that?
Thanks
Sql server version 8.00

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 13:51:48

So you just want the previous day's income or sum of all the previous days income for the same Id?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 13:55:00

I just want the previous days income,
Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 14:01:07

Replace @T with your table name.


UPDATE  t 
Set cumulativeincome = (Select Top 1 coalesce(cumulativeincome,0) From @t T2 Where t2.id = t.id and t2.dd < t.dd And T2.cumulativeincome is not null order by T2.dd desc )
from @t T 
Where T.cumulativeincome is null

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jeff06
Posting Yak Master

166 Posts

Posted - 2007-06-13 : 16:50:31

Cool, It works perfectly!
Thank you very much Danakar!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources