Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unanswered: Query Help..About Group by and Join

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try joining to itself in an exists subquery. something like

    Code:
    select cols from table a
    where exists (select 1 from table b
                       where a. end_date = b.start_date)
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •