Query Help. About Complex Join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help. About Complex Join

Author

Topic

mynameisfelix
Starting Member

7 Posts

Posted - 2010-05-12 : 15:45:47

Hi,

What I need to do basically is, I have to find out all the Names who started to work on "Job1" on the same day they completed their previous "Job1". For eg, in the below records, John Completed "Job1" on 4/20/2010(Ist Record) and again started to work on another "Job1" on 4/20/2010(3rd Record), so he should be selected in the result, similarly Steve completed "Job1" on 4/19/2010(8th Record) and again on the same day he started another "Job1"(9th Record) and so he should be selected in the result. But Mike although be worked on Job1 multiple times, he did not start Job1 on the same day he completed his previous Job1 and so he should not be selected.

Name |JobName | JobStart |JobCompleted
John |JOB1 | 4/19/2010 |4/20/2010
John |JOB2 | 4/19/2010 |4/19/2010
John |JOB1 | 4/20/2010 |4/21/2010
Mike |JOB1 | 4/21/2010 |4/22/2010
Mike |JOB1 | 4/23/2010 |4/24/2010
Mike |JOB1 | 4/25/2010 |4/26/2010
Mike |JOB1 | 4/27/2010 |4/28/2010
Steve |JOB1 | 4/19/2010 |4/19/2010
Steve |JOB1 | 4/19/2010 |4/20/2010
Steve |JOB2 | 4/23/2010 |4/24/2010
Steve |JOB3 | 4/23/2010 |4/24/2010
Steve |JOB3 | 4/24/2010 |4/25/2010
Ming |JOB3 | 4/23/2010 |4/24/2010
Ming |JOB3 | 4/24/2010 |4/25/2010

The result should look like this
Name |JobName | JobStart |JobCompleted
John |JOB1 | 4/19/2010 |4/20/2010
John |JOB1 | 4/20/2010 |4/21/2010
Steve |JOB1 | 4/19/2010 |4/19/2010
Steve |JOB1 | 4/19/2010 |4/20/2010

Thanks much,

FM

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-12 : 16:26:32

Specify the primary key in your table, to be able to distinguish rows from each others.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 16:44:44

[code]
SELECT name,
jobname,
jobstart,
jobcompleted
FROM (SELECT Row_number() OVER (ORDER BY name, jobname, jobstart, jobcompleted) AS rownum,
*
FROM your_tablename_here) t1
WHERE EXISTS(SELECT *
FROM (SELECT Row_number() OVER (ORDER BY name, jobname, jobstart, jobcompleted) AS rownum,
*
FROM your_tablename_here) t2
WHERE ( t1.jobstart = t2.jobcompleted
OR t1.jobcompleted = t2.jobstart )
AND t1.name = t2.name
AND t1.jobname = t2.jobname
AND t1.rownum <> t2.rownum)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 16:50:22

But malpashaa is right.
If there is a unique key in the table then the statement would look less complicated.

No, you're never too old to Yak'n'Roll if you're too young to die.

mynameisfelix
Starting Member

7 Posts

Posted - 2010-05-12 : 17:45:31

Thanks for the replies. The Primary Key would be WorkID. And I am specifically looking for JobName = "JOB1"

WorkID| Name |JobName | JobStart |JobCompleted
1|John |JOB1 | 4/19/2010 |4/20/2010
2|John |JOB2 | 4/19/2010 |4/19/2010
3|John |JOB1 | 4/20/2010 |4/21/2010
4|Mike |JOB1 | 4/21/2010 |4/22/2010
5|Mike |JOB1 | 4/23/2010 |4/24/2010
6|Mike |JOB1 | 4/25/2010 |4/26/2010
7|Mike |JOB1 | 4/27/2010 |4/28/2010
8|Steve |JOB1 | 4/19/2010 |4/19/2010
9|Steve |JOB1 | 4/19/2010 |4/20/2010
10|Steve |JOB2 | 4/23/2010 |4/24/2010
11|Steve |JOB3 | 4/23/2010 |4/24/2010
12|Steve |JOB3 | 4/24/2010 |4/25/2010
13|Ming |JOB3 | 4/23/2010 |4/24/2010
14|Ming |JOB3 | 4/24/2010 |4/25/2010

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-12 : 20:03:08

webfred solution with a little modification:

SELECT t.*
FROM table_name t
     CROSS APPLY
      (SELECT NULL
       FROM table_name
       WHERE Name = t.Name
       AND JobName = t.JobName
       AND WorkID <> t.WorkID
       AND (JobStart = t.JobCompleted OR JobCompleted = t.JobStart)D(i)
WHERE JobName = 'JOB1';

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-12 : 21:08:47

Just another approach:


SELECT CASE N.n WHEN 1 THEN Name1 ELSE Name2 END AS Name,
       CASE N.n WHEN 1 THEN JobName1 ELSE JobName2 END AS JobName,
       CASE N.n WHEN 1 THEN JobStart1 ELSE JobStart2 END AS JobStart,
       CASE N.n WHEN 1 THEN JobCompleted1 ELSE JobCompleted2 END AS Name
  FROM (SELECT J1.Name AS Name1, J1.JobName AS JobName1, J1.JobStart AS JobStart1, J1.JobCompleted AS JobCompleted1,
               J2.Name AS Name2, J2.JobName AS JobName2, J2.JobStart AS JobStart2, J2.JobCompleted AS JobCompleted2
         FROM Jobs AS J1
              INNER JOIN
              Jobs AS J2
              ON J2.WorkID <> J1.WorkID
                 AND J2.Name = J1.Name
                 AND J2.JobName = J1.JobName
                 AND J2.JobStart = J1.JobCompleted
        WHERE J1.JobName = 'Job1') AS T
       CROSS JOIN
       (SELECT 1 UNION ALL SELECT 2) AS N(n)

mynameisfelix
Starting Member

7 Posts

Posted - 2010-05-13 : 16:31:38

Thankyou Guys. It resolved my issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources