This discussion is archived
4 Replies Latest reply: Nov 30, 2012 4:25 AM by Solomon Yakobson RSS

Query Help.

suzvino Newbie
Currently Being Moderated
I am using 11g.

Need a SQL Query

I have a table like below
with t as
(
select 100 emp  , 1 assignment_id , to_date('27-OCT-2008','DD-MON-YYYY') start_date , to_date('31-DEC-2011','DD-MON-YYYY')  end_date from dual
union all
select 100 emp  , 1 assignment_id , to_date('01-JAN-2012','DD-MON-YYYY') start_date , to_date('30-SEP-2012','DD-MON-YYYY')  end_date from dual
union all
select 100 emp  , 2 assignment_id , to_date('01-OCT-2012','DD-MON-YYYY') start_date , to_date('26-NOV-2012','DD-MON-YYYY')  end_date from dual
union all
select 100 emp  , 2 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
union all
select 101 emp  , 3 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
union all
select 102 emp  , 4 assignment_id , to_date('27-OCT-2008','DD-MON-YYYY') start_date , to_date('31-DEC-2011','DD-MON-YYYY')  end_date from dual
union all
select 102 emp  , 4 assignment_id , to_date('01-JAN-2012','DD-MON-YYYY') start_date , to_date('30-SEP-2012','DD-MON-YYYY')  end_date from dual
union all
select 102 emp  , 5 assignment_id , to_date('01-OCT-2012','DD-MON-YYYY') start_date , to_date('26-NOV-2012','DD-MON-YYYY')  end_date from dual
union all
select 102 emp  , 5 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-2013','DD-MON-YYYY')  end_date from dual
union all
select 102 emp  , 6 assignment_id , to_date('01-JAN-2013','DD-MON-YYYY') start_date , to_date('15-JAN-2013','DD-MON-YYYY')  end_date from dual
union all
select 102 emp  , 6 assignment_id , to_date('16-JAN-2013','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
)
Whenever there is change in assignment_id i need the start date and end date of the old and new assignment

for the above table i need the output like this
Emp     Old_assignment_id  old_start_date old_end_date New_assignment_id  new_start_date new_end_date
100      1                      01-JAN-2012    30-SEP-2012   2                01-OCT-2012    26-NOV-2012
102      4               01-JAN-2012    30-SEP-2012   5                01-OCT-2012    26-NOV-2012
102      5               27-NOV-2012    31-DEC-2013   6                01-JAN-2013    15-JAN-2013
I dont want employee 101 since there is only one assignment id.
  • 1. Re: Query Help
    Solomon Yakobson Guru
    Currently Being Moderated
    Misunderstiood requirement.

    SY.

    Edited by: Solomon Yakobson on Nov 30, 2012 6:53 AM
  • 2. Re: Query Help.
    KeithJamieson Expert
    Currently Being Moderated
    with t as
    (
    select 100 emp  , 1 assignment_id , to_date('27-OCT-2008','DD-MON-YYYY') start_date , to_date('31-DEC-2011','DD-MON-YYYY')  end_date from dual
    union all
    select 100 emp  , 1 assignment_id , to_date('01-JAN-2012','DD-MON-YYYY') start_date , to_date('30-SEP-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 100 emp  , 2 assignment_id , to_date('01-OCT-2012','DD-MON-YYYY') start_date , to_date('26-NOV-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 100 emp  , 2 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
    union all
    select 101 emp  , 3 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 4 assignment_id , to_date('27-OCT-2008','DD-MON-YYYY') start_date , to_date('31-DEC-2011','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 4 assignment_id , to_date('01-JAN-2012','DD-MON-YYYY') start_date , to_date('30-SEP-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 5 assignment_id , to_date('01-OCT-2012','DD-MON-YYYY') start_date , to_date('26-NOV-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 5 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-2013','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 6 assignment_id , to_date('01-JAN-2013','DD-MON-YYYY') start_date , to_date('15-JAN-2013','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 6 assignment_id , to_date('16-JAN-2013','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
    )
    SELECT * 
    FROM
    (
     SELECT EMP, 
             LAG(ASSIGNMENT_ID,1,NULL) OVER (PARTITION BY EMP ORDER BY START_DATE) OLD_ASSIGNMENT_ID, 
            LAG(START_DATE,1,NULL) OVER (PARTITION BY EMP ORDER BY START_DATE) OLD_START_DATE,
            LAG(END_DATE,1,NULL) OVER (PARTITION BY EMP ORDER BY START_DATE) OLD_END_DATE ,
            ASSIGNMENT_ID NEW_ASSIGNMENT_ID, 
            START_DATE NEW_START_DATE,
            END_DATE NEW_END_DATE
     FROM T 
    )
    where new_assignment_id != old_assignment_id
    
    
    SQL> /
    
           EMP OLD_ASSIGNMENT_ID OLD_START OLD_END_D NEW_ASSIGNMENT_ID NEW_START NEW_END_D                                                                                                                                                                                                                      
    ---------- ----------------- --------- --------- ----------------- --------- ---------                                                                                                                                                                                                                      
           100                 1 01-JAN-12 30-SEP-12                 2 01-OCT-12 26-NOV-12                                                                                                                                                                                                                      
           102                 4 01-JAN-12 30-SEP-12                 5 01-OCT-12 26-NOV-12                                                                                                                                                                                                                      
           102                 5 27-NOV-12 31-DEC-13                 6 01-JAN-13 15-JAN-13                                                                                                                                                                                                                      
  • 3. Re: Query Help.
    Solomon Yakobson Guru
    Currently Being Moderated
    with t as
    (
    select 100 emp  , 1 assignment_id , to_date('27-OCT-2008','DD-MON-YYYY') start_date , to_date('31-DEC-2011','DD-MON-YYYY')  end_date from dual
    union all
    select 100 emp  , 1 assignment_id , to_date('01-JAN-2012','DD-MON-YYYY') start_date , to_date('30-SEP-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 100 emp  , 2 assignment_id , to_date('01-OCT-2012','DD-MON-YYYY') start_date , to_date('26-NOV-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 100 emp  , 2 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
    union all
    select 101 emp  , 3 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 4 assignment_id , to_date('27-OCT-2008','DD-MON-YYYY') start_date , to_date('31-DEC-2011','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 4 assignment_id , to_date('01-JAN-2012','DD-MON-YYYY') start_date , to_date('30-SEP-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 5 assignment_id , to_date('01-OCT-2012','DD-MON-YYYY') start_date , to_date('26-NOV-2012','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 5 assignment_id , to_date('27-NOV-2012','DD-MON-YYYY') start_date , to_date('31-DEC-2013','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 6 assignment_id , to_date('01-JAN-2013','DD-MON-YYYY') start_date , to_date('15-JAN-2013','DD-MON-YYYY')  end_date from dual
    union all
    select 102 emp  , 6 assignment_id , to_date('16-JAN-2013','DD-MON-YYYY') start_date , to_date('31-DEC-4712','DD-MON-YYYY')  end_date from dual
    ),
    t1 as (
    select  emp,
            assignment_id,
            min(start_date) start_date,
            max(end_date) end_date,
            lead(assignment_id) over(partition by emp order by assignment_id) new_assignment_id,
            lead(min(start_date)) over(partition by emp order by assignment_id) new_start_date,
            lead(max(end_date)) over(partition by emp order by assignment_id) new_end_date
      from  t
      group by emp,
               assignment_id
    )
    select  *
      from  t1
      where new_assignment_id is not null
    /
    
           EMP ASSIGNMENT_ID START_DAT END_DATE  NEW_ASSIGNMENT_ID NEW_START NEW_END_D
    ---------- ------------- --------- --------- ----------------- --------- ---------
           100             1 27-OCT-08 30-SEP-12                 2 01-OCT-12 31-DEC-12
           102             4 27-OCT-08 30-SEP-12                 5 01-OCT-12 31-DEC-13
           102             5 01-OCT-12 31-DEC-13                 6 01-JAN-13 31-DEC-12
    
    SQL> 
    SY.
  • 4. Re: Query Help.
    KeithJamieson Expert
    Currently Being Moderated
    Your start date is incorrect. The OP appears to want the preceding start_date, not the earliest start date for the assignment for each emp

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points