asked
How join this ??
DECLARE @Job TABLE (JobID INT, StartDate date, code int) INSERT INTO @job SELECT 1, '2010-10-20', 1 UNION ALL SELECT 2, '2010-10-21' ,2 UNION ALL SELECT 3, '2010-10-22' ,3 UNION ALL SELECT 4, '2010-10-21' ,1 UNION ALL SELECT 5, '2010-10-23' ,2 UNION ALL SELECT 6, '2010-10-24' ,2 DECLARE @Person TABLE (PersonID INT, JobID INT, PersonID INT) INSERT INTO @Person 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,2 The Output Expected is JobId, StartDate, PersonID 2 2010-10-21 1 3 2010-10-22 1 Conditions: 1.If code =1 that record should be eliminated from the resultset 2.For same personId the startDate should be within 48 hours of each other. expalning the scenario:if you examine for personId = 2 the output would be JobId, StartDate, PersonId, Code 4 2010-10-21 2 1 5 2010-10-23 2 2 since the condition is if code =1 eliminate it from result set. so we are left with the other record which dont have any other record within 48 hours of it start date and code !=1. So that record should not be in the output result. i tried many ways...but i was nowhere near it. Could any one plzzzzz help me out with it.
this is very similar to your other question here
http://ask.sqlservercentral.com/questions/21868/how-to-get-person-record-having-meetingdates-within-24-48hours-of-each-other
@leg i am sorry the first column is Pid(identity column).. DECLARE @Person TABLE (PID INT, JobID INT, PersonID INT) INSERT INTO @Person 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,2 @FatherJack.. Yes i did tried with that but could not achieve what am i looking for.. If you guys guide me with that...it would great. Thanks
Well how about taking the [answer @ThomasRushton gave you on your other question][1] and adding the filter on the new column `code`? DECLARE @Job TABLE (JobID INT, StartDate date, code int) INSERT INTO @job SELECT 1, '2010-10-20', 1 UNION ALL SELECT 2, '2010-10-21' ,2 UNION ALL SELECT 3, '2010-10-22' ,3 UNION ALL SELECT 4, '2010-10-21' ,1 UNION ALL SELECT 5, '2010-10-23' ,2 UNION ALL SELECT 6, '2010-10-24' ,2 DECLARE @Person TABLE (PerId INT, JobID INT, PersonID INT) INSERT INTO @Person 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,2 ; SELECT DISTINCT m.JobId, m.StartDate, mp.PersonID FROM @Job m LEFT JOIN @Person mp ON m.JobId = mp.JobId LEFT JOIN @Person mp2 ON mp.JobId <> mp2.JobId AND mp.PersonID = mp2.PersonID LEFT JOIN @Job m2 ON mp2.JobId = m2.JobId WHERE m2.JobId IS NOT NULL AND ABS(DATEDIFF(HOUR, m.StartDate, m2.StartDate)) <= 48 AND m.code<>1 -- < The new filter!! AND m2.code<>1 -- < The new filter!! [1]:
http://ask.sqlservercentral.com/questions/21868/how-to-get-person-record-having-meetingdates-within-24-48hours-of-each-other
Woop Woop here come the accept police. I have accepted William's answer on your behalf, @swethaashwini, but please do so yourself in the future.
Thank You William.. i have made a mistake while joining that is the reason i could not get the result. you saved me a lot of time by correcting me.
@swetha If you found William's answer helpful, could you please upvote and accept it? I would have upvoted it already, but currently I am out of votes for today, so I will do it a little later. Accepting the answer though can be done only by the person who asked the question, and if you accept it then it will help other users to find the best answers to their own questions.
@Oleg - no worries - happy campers are what it's all about :)
