5 Replies Latest reply: Feb 22, 2013 3:20 PM by user12997203 RSS

    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
        • 2. Re: breaking date ranges to continuous intervals and end date the previous row
          user12997203
          sorry,

          i am working on oracle 11.2.0.1
          • 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
              • 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