count with values from different rows

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)

count with values from different rows

Author

Topic

Elgasen
Starting Member

2 Posts

Posted - 2009-03-19 : 20:24:39

Hello!
My table look like this:
Column1 Column2
1 8
10 15
21 32
35 48

I would like to count diff between value in Column2 and the previous value in Column1.

like this:

Column1 Column2 Resultcolumn
1 8 0
10 15 2
21 32 6
35 48 3

How can I create this resultcolumn?

Br Elgasen!

matty
Posting Yak Master

161 Posts

Posted - 2009-03-20 : 00:45:39

DECLARE @t TABLE
(
Column1 int,
Column2 int
)
insert @t
select 1 ,8 union all
select 10, 15 union all
select 21, 32 union all
select 35, 48

SELECT A.Column1,A.Column2,ISNULL(A.Column1 - B.Column2,0) AS Resultcolumn
FROM
(SELECT Column1,Column2,ROW_NUMBER() OVER(ORDER BY Column1) AS Rownum FROM @t)A
LEFT JOIN
(SELECT Column1,Column2,ROW_NUMBER() OVER(ORDER BY Column1) AS Rownum FROM @t)B
ON A.rownum = B.rownum + 1

Note that I have sorted columns by Column1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources