Self join with date range - 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 2008 Forums

Transact-SQL (2008)

Self join with date range

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-24 : 08:25:32

Tab1
id sdate edate
1 01/01/10 03/03/10
1 25/01/10 25/03/10
1 02/03/10 25/04/10
1 26/04/10 30/05/10
2 01/01/10 03/03/10
2 25/01/10 25/03/10
3 02/03/10 25/04/10
4 26/04/10 30/05/10

i want to find out id details who have over lap date range with same id.
eg.
1 01/01/10 03/03/10
1 25/01/10 25/03/10
1 02/03/10 25/04/10

first 3 colum in the table. not 4th colum in the table.
what is the best solution(best query performance) are available.

regards,
Avijit

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 08:33:40

[code]SELECT t.id,t.sdate,t.edate
FROM Table t
OUTER APPLY (SELECT COUNT(1) AS Cnt
FROM Table
WHERE id = t.id
AND ((sdate >= t.sdate
AND sdate <= t.edate)
OR (sdate <= t.sdate
AND edate >= t.sdate))
)t1
WHERE t1.Cnt >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-24 : 09:52:52

quote:

Originally posted by visakh16

SELECT t.id,t.sdate,t.edate
FROM Table t
OUTER APPLY (SELECT COUNT(1) AS Cnt
             FROM Table
             WHERE id = t.id
             AND ((sdate >= t.sdate
             AND sdate <= t.edate)
             OR  (sdate <= t.sdate
             AND edate >= t.sdate))
             )t1
WHERE t1.Cnt >0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 11:05:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources