Difference between two Rows - 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

Difference between two Rows

Author

Topic

callinnn
Starting Member

7 Posts

Posted - 2007-05-21 : 03:30:45

My table is something like this

Col1 Balances
1000 1000
900 100
20 80
50 30

I want the balance to be calculated as (PreviousRecord
Balance-CurrentCol1)
Eg:1000-900=100
100-20=80
80-50=30

Please Help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 03:32:39

Do you have a primary key in the table ?

Are you using SQL Server 2000 or 2005 ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-21 : 03:43:39

Here is one solution (assuming you have primary key):

declare @t table
(
	pk int identity(1,1),	
	col1 int
)

insert @t(col1)
select 1000 union all
select 900 union all
select 20 union all
select 50 

select col1, (select sum(case when pk = 1 then col1 else col1 * -1 end) from @t t2 where t2.pk <= t1.pk) as Balance 
from @t t1 
order by pk

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

callinnn
Starting Member

7 Posts

Posted - 2007-05-21 : 04:06:35

The recordset that i m getting is
a result of some joins
yes i m using sql 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources