Difference between two rows in the same column

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 in the same column

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-30 : 12:05:35

Hi all,

I am new to SQL 2008 and am having trouble figuring out how to find the difference between two rows in the same column.

Data:

Real LossAmount fx
15 6087 0.05
12 2605 0.05
19 2308 0.05
11 2278 0.05
6 1770 0.05
18 1060 0.05
10 714 0.05
4 -3039 0.05
14 -3954 0.05
8 -6955 0.05
9 -7522 0.05
2 -8637 0.05
16 -9065 0.05
3 -11582 0.05
7 -13349 0.05
1 -14379 0.05
20 -17526 0.05
5 -17730 0.05
17 -21084 0.05
13 -23634 0.05

I want the following column:

dy
3,483 (6087-2605)
297 (2605-2308) and so on
30
508
710
346
3,754
914
3,001
568
1,115
427
2,518
1,767
1,030
3,148
203
3,355
2,549
-

The data is ordered by LossAmount in descending order and the name of the table is dbo.Data2

If anyone could help me with this topic, I would REALLY appreciate it! Thank you!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 12:13:38

with d as
(
select LossAmount, seq = row_number() over (order by LossAmount desc)
from Data2
)
select d.LossAmount - d2.LossAmount
from d d
join d d2
on dseq = d2.seq + 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-30 : 12:25:57

Thank you so much for your reply but SQL is not recognizing dseq as a column. Was that a typo?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 12:28:57

yes - shoould be d.seq
also shoould be
d2.seq = d.seq+1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-30 : 12:31:34

awesome. thank you so much for your help I really appreciate it!

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-30 : 13:54:14

One more thing:

select *,t1.amount-t.amount from (select ROW_NUMBER() over (order by (select (null)))rn ,amount from #tab)t
inner join (select ROW_NUMBER() over (order by (select (null)))rn ,amount from #tab) t1 on t.rn = t1.rn+1

Vijay is here to learn something from you guys.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-30 : 14:24:41

I'm confused as to how I add this column created from values on dbo.Data2 table to the table itself so it shows all 4 columns (Real, LossAmount, fx, dy) when I execute the code. Right now it's just displaying dy.
I updated the code to look like this: (named the column "dy")

with d as
(
select LossAmount, seq = row_number() over (order by LossAmount desc)
from dbo.Data2
)
select d.LossAmount - d2.LossAmount as dy
from d d
join d d2
on d2.seq = d.seq + 1

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-30 : 14:37:10

quote:
Originally posted by kotonikak

I'm confused as to how I add this column created from values on dbo.Data2 table to the table itself so it shows all 4 columns (Real, LossAmount, fx, dy) when I execute the code. Right now it's just displaying dy.
I updated the code to look like this: (named the column "dy")

with d as
(
select LossAmount, seq = row_number() over (order by LossAmount desc)
from dbo.Data2
)
select d.LossAmount - d2.LossAmount as dy
from d d
join d d2
on d2.seq = d.seq + 1

Do this:



with d as
	(
	select Real, LossAmount, fx,seq = row_number() over (order by LossAmount desc) 
	from dbo.Data2
	)
	select d.Real,d.LossAmount,d.fx,
        d.LossAmount - d2.LossAmount as dy 
	from d d
	join d d2
	on d2.seq = d.seq + 1

Vijay is here to learn something from you guys.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-05-30 : 14:48:47

thank you thank you!

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-06 : 09:54:07

There's 20 rows in this table and one problem about this query is that when it gets to row 20 and wants to do the difference between row 20 and the next row (row 21 which doesn't exist), instead of just displaying NULL, the row (row 20) is dropped completely and my results only display 19 rows. Is there any way I can have it recognize this and just have it display a 0 on the last row (since there is no difference between row 20 and the nonexistent row 21)?

i.e. get the following column:

dy
3,483 (6087-2605)
297 (2605-2308) and so on
30
508
710
346
3,754
914
3,001
568
1,115
427
2,518
1,767
1,030
3,148
203
3,355
2,549
0

thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-06 : 10:19:01

with d as
(
select Real, LossAmount, fx,seq = row_number() over (order by LossAmount desc)
from dbo.Data2
)
select d.Real,d.LossAmount,d.fx,
coalesce(d.LossAmount - d2.LossAmount,0) as dy
from d d
left join d d2
on d2.seq = d.seq + 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2012-06-06 : 11:52:08

thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources