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)
 Find Records With Overlapping Dates

Author  Topic 

phenreid
Starting Member

29 Posts

Posted - 2013-07-26 : 01:12:12
Consider table with fields:

GroupID
MemberID
StartDate
EndDate

I need to find all the records with overlapping dates within one GroupID+MemberID combination. For example, the following two records would be overlapping:

GroupID, MemberID, StartDate,EndDate
1234, 23467, 2/26/2010, 4/8/2013
1234, 23467, 4/1/2013 - 4/10/2013

The second record has a StartDate that is between the start/end date of the first record for the same GroupID/MemberID, so it is overlapping.

I already know how to do this with self-joins, so don't need help with that. I thought it could be done with a over/partition, but I couldn't figure it out because the argument to the "partion by" requires fixed fieldnames I believe. MSDN says PARTION BY "clause" but I don't know "clause" means in this context other than a list of fieldnames.

If there is a more modern and elegant way of doing this than self-joins, I would like to learn what is it? Thx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 02:12:26
Not in 2008. If you're on SQL 2012, there are ways.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-26 : 02:50:25
There are better and there are worse self joins. Which one are you using?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-26 : 03:31:11
[code]SELECT t1.GroupID,
t1.MemberID,
t1.StartDate,
t1.EndDate,
t2,StartDate,
t2.EnDate
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table1 AS t2 ON t2.GroupID = t1.GroupID
AND t2.MemberID = t1.MemberID
AND NOT (t2.StartDate = t1.StartDate AND t2.EndDate = t1.EndDate)
WHERE t2.StartDate <= t1.EndDate
AND t2.EndDate >= t1.StartDate;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

phenreid
Starting Member

29 Posts

Posted - 2013-07-26 : 15:24:34
Thx. I had done the self join exactly as you did, but appreciate confirmation.

A long time ago on this forum I asked how a certain thing was done and learned about the OVER/Partition when it was new. Over/Partition has made self-joins virtually obsolete for me since then, so now that I was out of the habit of using self-joins I wanted to know if they were ever needed. Apparently in 2008 they still are. Simple enough.

I'm not ready to move to 2012 yet and read about expanded features to the OVER/Partition - so I will look for that in the future. Thanks again.

Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-26 : 16:35:25
Just to clarify, you can still use OVER and PARTION BY clauses in 2008. But, that doesn't necessarily help solve your problem.
Go to Top of Page
   

- Advertisement -