This discussion is archived
3 Replies Latest reply: Mar 5, 2013 12:11 AM by 934476 RSS

Spliting Periods

934476 Newbie
Currently Being Moderated
Hi gurus,

I've a requirement and I can't find an easy way to do it.

I have one main table with a validity period for the data. I've a second table with also a validity period. And finally I've a table to maintain the relationship between these two tables. But the relationship will also have a validity period.

My goal is to build a summary table, with all the data from the main table, plus the data from the second table when data are valid in both table and the relationship between them is also valid.
So all the period should be splited in smaller period.

Here is an exemple (version on SQL Fiddle with Frank's solution : http://www.sqlfiddle.com/#!4/bb532/3)
Create table t1
(
key1 number,
a varchar2(10),
valid_from date,
valid_to date
);
 
INSERT INTO t1 VALUES (1, 'foo', to_date('20130101', 'yyyymmdd'), to_date('20130601', 'yyyymmdd'));
 
 
Create table t2
(
key2 number,
b varchar2(10),
valid_from date,
valid_to date
);
 
INSERT INTO t2 VALUES (10, 'bar', to_date('20130201', 'yyyymmdd'), to_date('20130401', 'yyyymmdd'));
INSERT INTO t2 VALUES (11, 'baz', to_date('20130501', 'yyyymmdd'), to_date('30001231', 'yyyymmdd'));
 
 
Create table rel_t1_t2
(
key1 number,
key2 number,
valid_from date,
valid_to date
);
 
INSERT INTO rel_t1_t2 VALUES (1, 10, to_date('20130201', 'yyyymmdd'), to_date('20130301', 'yyyymmdd'));
INSERT INTO rel_t1_t2 VALUES (1, 11, to_date('20130401', 'yyyymmdd'), to_date('30001231', 'yyyymmdd'));

commit;
Expected results :

Summary_Table_
|   A |      B |                FROM_DT |                 TO_DT |
------------------------------------------------------------------------------------
| foo | (null) |  January, 01 2013 | February, 01 2013  |
| foo |    bar | February, 01 2013 |    March, 01 2013  |
| foo | (null) |    March, 01 2013 |      May, 01 2013  |
| foo |    baz |      May, 01 2013 |     June, 01 2013  |
Is there a built-in feature in Oracle SQL to do that? Can we achieve it with a view?
If not, could we do that in PL/SQL? Is there a procedure shared somewhere ?



Thanks in advance for your help.

Best regards,
Jason


PS : I simplified the requirements here, but I actually have one main table with 4 secondary tables to enrich the first one. Each secondary table is linked to the main table through a relationship table. So I have 9 tables, and I need to build one summary table, with the periods splited in the smallest possible interval.

Edited by: 908016 on 04-Mar-2013 05:39
  • 1. Re: Spliting Periods
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Spliting Periods
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Jason,
    908016 wrote:
    Hi gurus,

    I've a requirement and I can't find an easy way to do it.

    I have one main table with a validity period for the data. I've a second table with also a validity period. And finally I've a table to maintain the relationship between these two tables. But the relationship will also have a validity period.

    My goal is to build a summary table, with all the data from the main table, plus the data from the second table when data are valid in both table and the relationship between them is also valid.
    So all the period should be splited in smaller period.

    Here is an exemple
    Create table t1
    (
    key number,
    a varchar2(10),
    from_date date,
    to_date date
    );
    TO_DATE is a terrible name for a column; it will get confused with the built-in function which is also called TO_DATE.
    INSERT INTO t1 VALUES (1, foo, to_date('20130101', 'yyyymmdd'), to_date('20130601', 'yyyymmdd'));
    Would you like people to post solutions that work? Then make sure the code you post works, too.
    Don't you need single-quotes around 'foo'?
    Create table t2
    (
    key number,
    b varchar2(10),
    from_date date,
    to_date date
    );
    
    INSERT INTO t2 VALUES (10, bar, to_date('20130201', 'yyyymmdd'), to_date('20130401', 'yyyymmdd'));
    INSERT INTO t2 VALUES (12, baz, to_date('20130501', 'yyyymmdd'), to_date('30001231', 'yyyymmdd'));
    
    
    Create table rel_t1_t2
    (
    key number,
    c varchar2(10),
    from_date date,
    to_date date
    );
    How are the tables joined?
    Is t2.key related to rel_t1_t2.c? If so, why is one a NUMBER, but the other a VARCHAR2?
    INSERT INTO rel_t1_t2 VALUES (1, 10, to_date('20130201', 'yyyymmdd'), to_date('20130301', 'yyyymmdd'));
    INSERT INTO rel_t1_t2 VALUES (1, 11, to_date('20130401', 'yyyymmdd'), to_date('30001231', 'yyyymmdd'));
    Is that 11 supposed to be 12?
    Expected results :

    Summary_Table_
    <tt>
    a     b    from_date  to_date
    foo 20130101 20130201
    foo bar 20130201 20130301
    foo 20130301 20130501
    foo baz 20130501 20130601
    </tt>
    Don't take the name "code" as in \
     tags too litterally.  It's okay to use \
    tags for query output, or any formatted text.
    Is there a built-in feature in Oracle SQL to do that? Can we achieve it with a view?
    If not, could we do that in PL/SQL? Is there a procedure shared somewhere ?
    I'm not sure exactly what your requirements are.
    This gets the right results from your sample data:
    WITH   modified_t2     AS
    (
         SELECT     r.key
         ,     t2.b
         ,     GREATEST (t2.from_dt, r.from_dt)     AS from_dt
         ,     LEAST      (t2.to_dt,   r.to_dt)          AS to_dt
         FROM     t2
         JOIN     rel_t1_t2  r  ON  TO_NUMBER (r.c) = t2.key
    )
    ,      overlaps          AS
    (
         SELECT     t1.a
         ,     m2.b
         ,     GREATEST (t1.from_dt, m2.from_dt)     AS from_dt
         ,     LEAST      (t1.to_dt,   m2.to_dt)          AS to_dt
         ,     t1.from_dt                          AS t1_from_dt
         FROM                         t1
         LEFT OUTER JOIN  modified_t2  m2  ON   m2.key       = t1.key
                                              AND  m2.from_dt <= t1.to_dt
                               AND  m2.to_dt       >= t1.from_dt
    )
    SELECT       a, b, from_dt, to_dt
    FROM       overlaps
           --
        UNION ALL
           --
    SELECT    a
    ,       NULL               AS b
    ,       LAG ( to_dt
               , 1
               , t1_from_dt
               ) OVER ( PARTITION BY  a
                           ORDER BY          from_dt
                   )                AS from_dt
    ,       from_dt              AS to_dt
    FROM       overlaps
           --
    ORDER BY  a
    ,            from_dt
    ;
    What if rows in t2 overlap? What if to_dt from one row in t2 is exactly the same as from_dt of the next?
    I'm sure I made some bad assumptions, but whatever you really need, I'll bet you can do it using only pure SQL, without any PL/SQL.
  • 3. Re: Spliting Periods
    934476 Newbie
    Currently Being Moderated
    Thank you for your answer Frank.
    I apologize for the errors in my initial post, I unfortunately had no DB available at this time. I updated it for future readers.


    Your solution does exactly what I needed : http://www.sqlfiddle.com/#!4/bb532/3.
    Thank you very much!


    Unfortunately I tried to reproduce it with a third table (see link above) and didn't manage to get the expected result. I always have a main table and I can have up to 6 other tables to enrich it (with validity period for data + validity period for the relationship).
    Any idea ?



    For your further questions:
    - rows in t2 should not overlap.
    - valid_to date is excluded from the period so it is not a problem if it is the same as the next valid_from date. So a date d is in the period if valid_from <= d < valid_to



    Best regards,
    Jason

Legend

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