x

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.

more ▼

asked Oct 21, 2010 at 12:27 PM in Default

swethaashwini gravatar image

swethaashwini
223 17 18 20

@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.
Oct 21, 2010 at 12:56 PM Oleg

@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
Oct 21, 2010 at 01:31 PM swethaashwini
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Oct 21, 2010 at 01:42 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.
Oct 21, 2010 at 02:15 PM swethaashwini
@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.
Oct 21, 2010 at 03:19 PM Oleg
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.
Oct 21, 2010 at 03:22 PM Matt Whitfield ♦♦
@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 :)
Oct 21, 2010 at 03:26 PM Oleg
@Oleg - no worries - happy campers are what it's all about :)
Oct 21, 2010 at 04:25 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x118

asked: Oct 21, 2010 at 12:27 PM

Seen: 661 times

Last Updated: Oct 21, 2010 at 12:58 PM