4 Replies Latest reply: Nov 30, 2012 6:25 AM by Solomon Yakobson RSS

    Query Help.

    suzvino
      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
          Misunderstiood requirement.

          SY.

          Edited by: Solomon Yakobson on Nov 30, 2012 6:53 AM
          • 2. Re: Query Help.
            Keith Jamieson
            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
              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.
                Keith Jamieson
                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