TSQL Help - 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 2008 Forums

Transact-SQL (2008)

TSQL Help

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-06-17 : 22:38:32

Hi, Here is my source

ID AMOUNT
1 100
2 200
3 50
4 100

AND i want end result like this

ID AMOUNT FULL_AMOUNT
1 100 100
2 200 300
3 50 350
4 100 450

Thanks for help.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-06-18 : 02:45:21

create table rec(id int identity(1,1), amount int, full_amount int)
insert into rec(amount) select 100
insert into rec(amount) select 200
insert into rec(amount) select 50
insert into rec(amount) select 100
insert into rec(amount) select 50
insert into rec(amount) select 200
insert into rec(amount) select 100
insert into rec(amount) select 50
insert into rec(amount) select 0
insert into rec(amount) select 100

table is as follows:

id amount full_amount
----------- ----------- -----------
1 100 NULL
2 200 NULL
3 50 NULL
4 100 NULL
5 50 NULL
6 200 NULL
7 100 NULL
8 50 NULL
9 0 NULL
10 100 NULL

(10 row(s) affected)

Now run the following code:

declare @i int,@j int, @k int, @x int,@temp int
set @i=(select COUNT(*) from rec)
set @j=1
set @k= (select amount from rec where id=1)
set @x=1
set @temp=0

while(@j<=@i)
begin
set @x = (select amount from rec where id=+@j)
set @temp= @temp+ @x
exec('update rec set full_amount='+@temp+'where id='+@j)
set @j=@j+1

end

The result of the query is:

id amount full_amount
----------- ----------- -----------
1 100 100
2 200 300
3 50 350
4 100 450
5 50 500
6 200 700
7 100 800
8 50 850
9 0 850
10 100 950

(10 row(s) affected)

Which is what you want..enjoy :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-18 : 06:28:31

If you have a large number of rows, it would be more efficient to use a set-based query such as this:


;with cte as
(
	select id,amount,amount as full_amount from rec where id=1
	union all
	select
		r.id,r.amount,r.amount+c.full_amount
	from
	    rec r inner join cte c on r.id = c.id+1
)
--select * from cte option (maxrecursion 0);
update r set r.full_amount = c.amount
from rec r inner join cte c on c.id = r.id option (maxrecursion 0);

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-20 : 12:30:29

Youc an do a search for Running Total and get lots ways. Here is a link to one:
http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-20 : 12:36:26

An easy way to do this is to do at front end application

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources