5 Replies Latest reply on Feb 22, 2013 9:20 PM by user12997203

    breaking date ranges to continuous intervals and end date the previous row

    user12997203
      Hi
      can any one give me an idea for below situation

      i have a table with records
      CREATE  TABLE test1
        (
          key VARCHAR2(11 BYTE) NOT NULL ENABLE,
         cd VARCHAR2(10 BYTE) NOT NULL ENABLE,
              start_dt DATE NOT NULL ENABLE,
          end_dt date,
          CONSTRAINT x1 PRIMARY KEY (key, cd, start_dt));
          
      Insert into test1 (key,cd,start_dt,end_dt) values ('1234','A',to_date('01-MAR-78 00:00:00','DD-MON-RR hh24:mi:ss'),to_date('31-DEC-99 00:00:00','DD-MON-RR hh24:mi:ss'));
      Insert into test1 (key,cd,start_dt,end_dt) values ('1234','B',to_date('01-MAR-78 00:00:00','DD-MON-RR hh24:mi:ss'),to_date('31-DEC-99 00:00:00','DD-MON-RR hh24:mi:ss'));
      Insert into test1 (key,cd,start_dt,end_dt) values ('1234','Q',to_date('01-JAN-89 00:00:00','DD-MON-RR hh24:mi:ss'),to_date('31-JAN-97 00:00:00','DD-MON-RR hh24:mi:ss'));
      Insert into test1 (key,cd,start_dt,end_dt) values ('1234','B',to_date('01-FEB-97 00:00:00','DD-MON-RR hh24:mi:ss'),to_date('30-APR-97 00:00:00','DD-MON-RR hh24:mi:ss'));
      insert into test1(key,cd,start_dt,end_dt) values ('1234','Q',to_date('01-FEB-97 00:00:00','DD-MON-RR hh24:mi:ss'),to_date('30-APR-97 00:00:00','DD-MON-RR hh24:mi:ss'));
      Insert into test1 (key,cd,start_dt,end_dt) values ('1234','B',to_date('01-MAY-97 00:00:00','DD-MON-RR hh24:mi:ss'),to_date('31-MAR-99 00:00:00','DD-MON-RR hh24:mi:ss'));
      commit;
      
      key     cd     start_dt                     END_DT
      1234     B     01-MAR-78 00:00:00     31-DEC-99 00:00:00
      1234     A     01-MAR-78 00:00:00     31-DEC-99 00:00:00
      1234     Q     01-JAN-89 00:00:00     31-JAN-97 00:00:00
      1234     Q     01-FEB-97 00:00:00     30-APR-97 00:00:00
      1234     B     01-FEB-97 00:00:00     30-APR-97 00:00:00
      1234     B     01-MAY-97 00:00:00     31-MAR-99 00:00:00
      i want to merge the overlapping date range rows,break them in to intervals and set the indicator(A_IND,B_IND,Q_IND) accordingly.(i.e if a record in source has a value for column "cd"='B' in a particular date range then the output should show 'Y' for column B_IND for the date ranges in source record.)
      also i want the end date of each record to be 1 day less than the start_dt of next record as shown below.
      and the output should be
      key     start_dt                          END_DT     A_IND     B_IND     Q_IND
      1234     01-MAR-78 00:00:00     31-DEC-88 00:00:00     Y     Y     N
      1234     01-JAN-89 00:00:00     30-JAN-97 00:00:00     Y     Y     Y
      1234     31-JAN-97 00:00:00     31-JAN-97 00:00:00     Y     Y     N
      1234     01-FEB-97 00:00:00     29-APR-97 00:00:00     Y     Y     Y
      1234     30-APR-97 00:00:00     30-APR-97 00:00:00     Y     Y     N
      1234     01-MAY-97 00:00:00     30-MAR-99 00:00:00     Y     Y     N
      1234     31-MAR-99 00:00:00     31-DEC-99 00:00:00     Y     Y     N
        • 3. Re: breaking date ranges to continuous intervals and end date the previous row
          Frank Kulash
          Hi,
          user12997203 wrote:
          Hi
          can any one give me an idea for below situation

          i have a table with records
          CREATE  TABLE test1 ...
          Thanks for posting the sample data; that's very helpful.
          i want to merge the overlapping date range rows,break them in to intervals and set the indicator(A_IND,B_IND,Q_IND) accordingly.(i.e if a record in source has a value for column "cd"='B' in a particular date range then the output should show 'Y' for column B_IND for the date ranges in source record.)
          Don't assume that everyone who wants to help you is familiar with your application and your requirements. Explain what role key plays in this problem. See {message:id=10865697}.
          Here's one way:
          WITH     change_points       AS
          (
               SELECT     key
               ,     start_dt     AS dt
               FROM     test1
              UNION
               SELECT     key
               ,     end_dt          AS dt
               FROM     test1
          )
          ,     ranges          AS
          (
               SELECT  key
               ,     dt                    AS start_dt
               ,     LEAD (dt)     OVER ( PARTITION BY  key
                                              ORDER BY          dt
                                 )          AS next_dt 
               FROM     change_points
          )
          SELECT    r.key
          ,       r.start_dt
          ,       r.next_dt - CASE
                               WHEN  r.next_dt = MAX (r.next_dt) OVER ()
                          THEN  0
                          ELSE  1
                            END                         AS end_dt
          ,       MAX (CASE WHEN t.cd = 'A' THEN 'Y' ELSE 'N' END)     AS a_ind
          ,       MAX (CASE WHEN t.cd = 'B' THEN 'Y' ELSE 'N' END)     AS b_ind
          ,       MAX (CASE WHEN t.cd = 'Q' THEN 'Y' ELSE 'N' END)     AS q_ind
          FROM       ranges     r
          JOIN       test1          t  ON   t.key          = r.key
                              AND     t.start_dt     < r.next_dt
                            AND     t.end_dt     > r.start_dt
          GROUP BY  r.key
          ,            r.start_dt
          ,       r.next_dt
          ORDER BY  r.start_dt
          ;
          Note that this is dependent on the values you want in the a_ind, b_ind and q_ind columns. For example, if you were doing this for German-speaking users, you might want to dispaly 'J' and 'N' instead of 'Y' and 'N'. In that case, you would use MIN instead of MAX, to get the dominant value.
          • 4. Re: breaking date ranges to continuous intervals and end date the previous row
            chris227
            Ok, you just wanted an idea, thats creditable ;-)
            with ranges as (
            select
             start_dt sd
            ,lead(start_dt,1,to_date('31122099','DDMMYYYY')) over (partition by key order by start_dt) - 1
             ed
            from
            test1
            )
            
            select
            *
            from (
            select
             sd
            ,ed
            ,key
            ,cd
            from
             test1
            ,ranges
            where
            ranges.sd <= test1.end_dt
            and
            ranges.ed >= test1.start_dt
            )
            pivot (
            count(
            1
            ) for cd in ('A','B','Q')
            )
            order by sd
            
            SD     ED     KEY     'A'     'B'     'Q'
            03/01/1978     12/31/1988     1234     1     1     0
            01/01/1989     01/31/1997     1234     1     1     1
            02/01/1997     01/31/1997     1234     1     1     0
            02/01/1997     04/30/1997     1234     1     2     1
            05/01/1997     12/30/2099     1234     1     2     0
            1 person found this helpful
            • 5. Re: breaking date ranges to continuous intervals and end date the previous row
              user12997203
              Thanks,Frank.
              Key is nothing but a list of id's or members.

              Edited by: user12997203 on Feb 22, 2013 1:19 PM