JOIN onto itself and contains the lower date

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)

JOIN onto itself and contains the lower date

Author

Topic

CORRUPT27
Starting Member

4 Posts

Posted - 2008-06-02 : 16:52:21

Hello,
I have been having a hard time with this issue. I am attempting to join a table onto itself to get the closest date onto a single row.
What i mean is:
I have the following data
id date
1 10/07/08
2 10/06/07
3 10/06/03
4 10/06/03

the new table should have the current id and the one closes to it as so.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
3 10/06/03 null null
4 10/06/03 null null
but i am getting duplicates do to the 10/06/03.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
2 10/06/07 4 10/06/03
3 10/06/03 null null
4 10/06/03 null null
i want so that if there is a duplicate i can take the id thats higher. I cant figure it out.
This is my current sql:

SELECT PB.ID,PB.StartDate, PB2.ID, PB2.Startdate
from table PB
left outer join table PB2 on PB.keyID = PB2.keyID
and PB2.StartDate < PB.StartDate
and PB.StartDate = (select top(1) StartDate from table PB3 where PB.keyID = PB3.keyID
and PB2.StartDate < PB3.StartDate order by PB3.StartDate asc)

Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 00:27:29

[code]DECLARE @Temp table
(id int,
date datetime
)

INSERT INTO @Temp
SELECT 1 ,'10/07/08'
UNION ALL
SELECT 2, '10/06/07'
UNION ALL
SELECT 3, '10/06/03'
UNION ALL
SELECT 4, '10/06/03'

SELECT t.id,t.date,b.id,b.date
FROM @Temp t
OUTER APPLY (SELECT ROW_NUMBER() OVER(ORDER BY date DESC)AS RowNo,
id,date
FROM @Temp
WHERE date <t.date)b
WHERE b.RowNo=1 or b.RowNo IS NULL

id date id date
----------- ----------------------- ----------- -----------------------
1 2008-10-07 00:00:00.000 2 2007-10-06 00:00:00.000
2 2007-10-06 00:00:00.000 3 2003-10-06 00:00:00.000
3 2003-10-06 00:00:00.000 NULL NULL
4 2003-10-06 00:00:00.000 NULL NULL[/code]

CORRUPT27
Starting Member

4 Posts

Posted - 2008-06-03 : 09:17:07

Thank you so much that was beautiful.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-03 : 11:45:09

This may be more efficient:

SELECT t.id,t.date,b.id,b.date
FROM @Temp t
OUTER APPLY (SELECT top 1 id,date
FROM @Temp
WHERE date <t.date ORDER BY date DESC)b

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources