SQL Statement 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 2000 Forums

Transact-SQL (2000)

SQL Statement Help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-14 : 08:36:09

Rama Prasad writes "Hi Experts

Need an advise from you, I have a table with data:
ID Transaction
1 01-Jan-2007
1 02-Jan-2007
1 03-Jan-2007
2 10-Jan-2007
2 11-Jan-2007
I want to write a SQL Statement whose output to be:
ID Previous Date Next Date
1 01-Jan-2007 02-Jan-2007
1 02-Jan-2007 03-Jan-2007
2 10-Jan-2007 11-Jan-2007

Can you please Help?

Thankyou in advance"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 09:03:19

[code]
declare @table table
(
[ID] int,
[Transaction] datetime
)

insert into @table
select 1, '01-Jan-2007' union all
select 1, '02-Jan-2007' union all
select 1, '03-Jan-2007' union all
select 2, '10-Jan-2007' union all
select 2, '11-Jan-2007'

select p.[ID],
p.[Transaction] as [Previous Date],
min(n.[Transaction]) as [Next Date]
from @table p inner join @table n
on p.ID = n.ID
and p.[Transaction] < n.[Transaction]
group by p.[ID], p.[Transaction]

/*
ID Previous Date Next Date
----------- ------------------- -----------
1 2007-01-01 2007-01-02
1 2007-01-02 2007-01-03
2 2007-01-10 2007-01-11
*/
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 09:33:03

[code]select t1.id,
t1.[transaction],
t2.[transaction]
from @table as t1
left join @table as t2 on t2.id = t1.id and t2.[transaction] - 1 = t1.[transaction]
where t2.id is not null
order by 1,
2[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources