Select the next date in a table for an employee

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)

Select the next date in a table for an employee

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-10 : 09:43:25

Jason writes "Hi, I have a table that contains multiple dates for each employee

e.g.

Ref No Date
001 1/1/2002
001 31/12/2003
002 30/6/1999

I need a query that will output each row, with the next date for that Ref if there is one

i.e.

Ref No Date NextDate
001 1/1/2002 31/12/2003
002 30/6/1999 Null

I've tried various sub queries but I can't get any of them to work.

Can you help?

Cheers,

Jason"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-10 : 10:13:56

[code]declare @table table
(
Ref_No varchar(10),
Date datetime
)
set dateformat dmy
insert into @table
select '001', '1/1/2002' union all
select '001', '31/12/2003' union all
select '002', '30/6/1999'

select Ref_No, min(Date) as [Date],
(select max([Date]) from @table x
where x.Ref_No = t.Ref_No
and x.Date <> (select min(Date) from @table y where y.Ref_No = t.Ref_No)) as [NextDate]
from @table t
group by Ref_No

/*
Ref_No Date NextDate
---------- ------------------------ -----------------------
001 2002-01-01 00:00:00.000 2003-12-31 00:00:00.000
002 1999-06-30 00:00:00.000 NULL
*/[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources