3 Replies Latest reply: Mar 5, 2013 2:11 AM by 934476 RSS

    Spliting Periods

    934476
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Spliting Periods
            Frank Kulash
            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
              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