SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find Records With Overlapping Dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phenreid
Starting Member

29 Posts

Posted - 07/26/2013 :  01:12:12  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/26/2013 :  02:12:26  Show Profile  Reply with Quote
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

Sweden
30208 Posts

Posted - 07/26/2013 :  02:50:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30208 Posts

Posted - 07/26/2013 :  03:31:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



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

phenreid
Starting Member

29 Posts

Posted - 07/26/2013 :  15:24:34  Show Profile  Reply with Quote
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.


Edited by - phenreid on 07/27/2013 02:12:34
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/26/2013 :  16:35:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000