question

swethaashwini avatar image
swethaashwini 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.
joins
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@swethaashwini Could you please check your @Person definition? You have 2 columns there with the same name, namely both first and third columns are named PersonID.
3 Likes 3 ·
swethaashwini avatar image swethaashwini commented ·
@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
0 Likes 0 ·

1 Answer

·
WilliamD avatar image
WilliamD answered
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
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
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.
1 Like 1 ·
swethaashwini avatar image swethaashwini commented ·
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.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@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.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Matt Whitfield I replied to your email Matt, the new version solved it. Thank you very much for your help, I am a happy camper now :)
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Oleg - no worries - happy campers are what it's all about :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.