2 Replies Latest reply on Sep 4, 2014 8:07 PM by Syed Ullah

    Query help

    Syed Ullah

      Hi eveyone! Hope one of you can help me with a query.

       

       

      I have a table that looks like the following:

       

      customer    status    status_date

      ----------------------------------------------

      a           x               8/1/2014

      a           z               8/12/2014

      a           x               8/15/2014

      b           y               8/1/2014

      c           x               8/5/2014

      c           x               8/15/2014

       

       

      I am looking for a query that will give a result like the following:

       

      customer    status    start_date    end_date

      --------------------------------------------------------------

      a                x          08/01/2014    08/11/2014

      a                z          08/12/2014    08/14/2014

      a                x          08/15/2014

      b                y          08/01/2014

      c                x          08/05/2014

       

       

      Basically, for each customer, a status and a date is reported. Until a different status is

      reported for the same customer, he maintains that status (signified by a null end_date). If a

      different status is reported, the earlier status will be assigned a date range (from status_date

      to the date prior to the different status date).

       

       

      Here are the 'create' and 'insert' statements. Database version is 11g. Thanks in advance!

       

      create table test_table(

          customer        varchar2(20),

          status             varchar2(20),

          status_date     date

          );

       

      insert into test_table values ('a', 'x', to_date('08/01/2014', 'mm/dd/yyyy'));

      insert into test_table values ('b', 'y', to_date('08/01/2014', 'mm/dd/yyyy'));

      insert into test_table values ('a', 'z', to_date('08/12/2014', 'mm/dd/yyyy'));

      insert into test_table values ('a', 'x', to_date('08/15/2014', 'mm/dd/yyyy'));

      insert into test_table values ('c', 'x', to_date('08/05/2014', 'mm/dd/yyyy'));

      insert into test_table values ('c', 'x', to_date('08/15/2014', 'mm/dd/yyyy'));


        • 1. Re: Query help
          Frank Kulash

          Hi,

           

          So, you need to compute end_date based on the status_date of the next row for the same customer.  That sound like a job for the analytic LEAD function.

          But when a row has the same status as the previous row for the same customer, you want to ignore that row.  That sounds like a job for the analytic LAG function.

          Here's one way to do that:

           

          WITH    got_diff    AS

          (

              SELECT  customer, status, status_date

              ,       CASE

                          WHEN  status = LAG (status) OVER ( PARTITION BY  customer

                                                             ORDER BY      status_date

                                                           )

                          THEN  0

                          ELSE  1

                      END     AS diff

              FROM    test_table

          )

          SELECT    customer

          ,         status

          ,         status_date                       AS start_date

          ,         LEAD (status_date) OVER ( PARTITION BY  customer

                                              ORDER BY      status_date

                                            ) - 1     AS end_date

          FROM      got_diff

          WHERE     diff  = 1

          ;

          Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful!

          • 2. Re: Query help
            Syed Ullah

            Hi Frank.

             

            Thanks a lot for your creative solution!!