Difference of a concecutive 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 2000 Forums

Transact-SQL (2000)

Difference of a concecutive rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-12 : 11:15:05

venkat writes "suppose I have one table with two fields of Date and Temprature
like this

6/22/2006 35
6/23/2006 37
6/24/2006 40
6/25/2006 43
6/26/2006 47
6/27/2006 55
6/28/2006 59
6/29/2006 73
6/30/2006 89

I want to select the query like below format

DateDiff Temp

6/23/2006 - 6/22/2006 2
6/24/2006 - 6/23/2006 3
6/25/2006 - 6/24/2006 3
....
....
Like this
how can I write the query
Please Give a Proper answer"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 11:26:19

[code]declare @table table
(
dte datetime,
tmp int
)
insert into @table
select '6/22/2006', 35 union all
select '6/23/2006', 37 union all
select '6/24/2006', 40 union all
select '6/25/2006', 43 union all
select '6/26/2006', 47 union all
select '6/27/2006', 55 union all
select '6/28/2006', 59 union all
select '6/29/2006', 73 union all
select '6/30/2006', 89

select convert(varchar(10), t1.dte, 101) + ' - ' + convert(varchar(10), t2.dte, 101) as DteDiff,
t1.tmp - t2.tmp as TmpDiff
from @table t1 inner join @table t2
on t1.dte > t2.dte
and t2.dte = (select max(dte) from @table x where x.dte < t1.dte)

/* RESULT
DteDiff TmpDiff
----------------------- -----------
06/23/2006 - 06/22/2006 2
06/24/2006 - 06/23/2006 3
06/25/2006 - 06/24/2006 3
06/26/2006 - 06/25/2006 4
06/27/2006 - 06/26/2006 8
06/28/2006 - 06/27/2006 4
06/29/2006 - 06/28/2006 14
06/30/2006 - 06/29/2006 16
*/[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources