asked
How to get person record having meetingdates within 24-48hours of each other.
Hi Guys, i have a issue and couldn't get to a solution. Any one of you please help me out. The issue goes this way. table 1 meeting id meeting_start_date 1 2010-10-20 2 2010-10-21 3 2010-10-22 4 2010-10-21 5 2010-10-24 table 2. meeting_person_id meeting_id person id 1 1 1 2 2 1 3 3 1 4 4 2 5 5 3 output should be as shown below. meeting_id meeting_start_date person_id 1 2010-10-20 1 2 2010-10-21 1 3 2010-10-22 1 we need to get person_id only when the meeting_start_date for the same person is within in 24-48 hours of each other. How to accomplish this??? Thanks
this is very similar to your other question here
http://ask.sqlservercentral.com/questions/26506/how-join-this
Hint: the "ON" clause of a "JOIN" doesn't have to be a straight equality... EDIT: This code seems to work: First, set up the temporary tables: DECLARE @Meeting TABLE (MeetingID INT, MeetingStartDate date) INSERT INTO @Meeting SELECT 1, '2010-10-20' UNION ALL SELECT 2, '2010-10-21' UNION ALL SELECT 3, '2010-10-22' UNION ALL SELECT 4, '2010-10-21' UNION ALL SELECT 5, '2010-10-24' DECLARE @MeetingPerson TABLE (MeetingPersonID INT, MeetingID INT, PersonID INT) INSERT INTO @MeetingPerson SELECT 1,1,1 UNION ALL SELECT 2,2,1 UNION ALL SELECT 3,3,1 UNION ALL SELECT 4,4,2 UNION ALL SELECT 5,5,3 And now on to the query itself: SELECT DISTINCT m.MeetingID, m.MeetingStartDate, mp.PersonID FROM @Meeting m LEFT JOIN @MeetingPerson mp ON m.MeetingID = mp.MeetingID LEFT JOIN @MeetingPerson mp2 ON mp.MeetingID mp2.MeetingID AND mp.PersonID = mp2.PersonID LEFT JOIN @Meeting m2 ON mp2.MeetingID = m2.MeetingID WHERE m2.MeetingID IS NOT NULL -- EDIT: Changed following issues with other data... -- ...mind you, I should have done this anyway! -- AND DATEDIFF(HOUR, m.MeetingStartDate, m2.MeetingStartDate)
Show more comments
I suspect if I replaced ... mp.MeetingID mp2.MeetingID ... with ... mp.MeetingID < mp2.MeetingID ... I could get rid of the "DISTINCT", which should improve performance of the query.
I stand corrected. Or, rather, sit corrected... Thanks @WilliamD!
Thanks Thomas... But i donno i still having the troubles with the output records... i am getting records like.. 2010-07-21 2010-07-25 which is no expected in the output.. like wise i have noticed some more records in my resultset. Could you suggest me how to get rid of them.
@swethaashwini - It's possible that the data you posted above doesn't completely represent the data you've actually got, and I've (as a result) missed something. Are you using the version in my original response, or with the alteration that @WilliamD said would break things? Just checking... ;-)
Well here is my take on it using recursion. Maybe not too nice on a big data-set YMMV. I have based it on the extra comments made by swethaashwini under Thomas' answer: DECLARE @Meeting TABLE (MeetingID INT , MeetingStartDate DATE) INSERT INTO @Meeting SELECT 1 ,'2010-10-20' UNION ALL SELECT 2 ,'2010-10-21' UNION ALL SELECT 3 ,'2010-10-22' UNION ALL SELECT 4 ,'2010-10-21' UNION ALL SELECT 5 ,'2010-10-24' UNION ALL SELECT 6 ,'2010-10-28' DECLARE @MeetingPerson TABLE ( MeetingPersonID INT , MeetingID INT , PersonID INT ) INSERT INTO @MeetingPerson SELECT 1 ,1 ,1 UNION ALL SELECT 2 ,2 ,1 UNION ALL SELECT 3 ,3 ,2 UNION ALL SELECT 4 ,4 ,2 UNION ALL SELECT 5 ,5 ,1 UNION ALL SELECT 6 ,6 ,1 ; WITH Grouper AS ( SELECT MP.PersonID , MP.MeetingID , M.MeetingStartDate , RN = ROW_NUMBER() OVER ( PARTITION BY MP.PersonID ORDER BY MeetingStartDate ) FROM @MeetingPerson MP INNER JOIN @Meeting M ON MP.MeetingID = M.MeetingID ), recurs AS ( SELECT PersonID , MeetingID , MeetingStartDate , diff = DATEDIFF(HOUR, MeetingStartDate, MeetingStartDate) , RN FROM Grouper WHERE RN = 1 UNION ALL SELECT l2.PersonID , l2.MeetingID , l2.MeetingStartDate , diff = DATEDIFF(HOUR, l1.MeetingStartDate, l2.MeetingStartDate) , l2.RN FROM Grouper l2 INNER JOIN recurs l1 ON l2.PersonID = l1.PersonID AND l2.RN = l1.RN + 1 ) SELECT MeetingID , MeetingStartDate , PersonID FROM recurs WHERE diff
Show more comments
@Scot - the "-- " at the start of the line indicates it's commented out... ;-)
+1 'cos it works. Not sure why, mind - I still haven't got the hang of recursion in T-SQL! The disadvantage of this is that it is more "expensive" - even with just the small amount of data we've been given...
I would be surprised if recursion beats a couple LEFT JOINs. In my xp recursion tends to be slower with larger row sets. @Thomas you can remove the AND DATEDIFF(... predicate, it is redundant after adding the ABS
