This discussion is archived
5 Replies Latest reply: Feb 22, 2013 1:20 PM by user12997203 RSS

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

user12997203 Newbie
Currently Being Moderated
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
  • 1. Re: breaking date ranges to continuous intervals and end date the previous row
    chris227 Guru
    Currently Being Moderated
    4-digit-DB-Version,please
  • 2. Re: breaking date ranges to continuous intervals and end date the previous row
    user12997203 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks,Frank.
    Key is nothing but a list of id's or members.

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

Legend

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