Search code examples
sqloracle-databaseoracle19c

Create date periods based on two tables

CREATE TABLE "TEST1" 
(   
    "ID" NUMBER(9,0) NOT NULL ENABLE, 
    "DATE_FROM" DATE, 
    "DATE_TO" DATE
);

CREATE TABLE "TEST2" 
(   
    "ID" NUMBER, 
    "PERCENT" NUMBER(5,4), 
    "DATE_FROM" DATE
);
   
INSERT INTO test1 (ID, DATE_FROM, DATE_TO)
VALUES (546, to_date('01-12-2005', 'dd-mm-yyyy'), to_date('01-12-2006', 'dd-mm-yyyy'));

INSERT INTO test1 (ID, DATE_FROM, DATE_TO)
VALUES (546, to_date('01-12-2006', 'dd-mm-yyyy'), to_date('01-07-2011', 'dd-mm-yyyy'));

INSERT INTO test1 (ID, DATE_FROM, DATE_TO)
VALUES (546, to_date('01-07-2011', 'dd-mm-yyyy'), to_date('31-12-4712', 'dd-mm-yyyy'));

INSERT INTO test2 (ID, PERCENT, DATE_FROM)
VALUES (546, 0.0100, to_date('01-12-2005', 'dd-mm-yyyy'));

INSERT INTO test2 (ID, PERCENT, DATE_FROM)
VALUES (546, 0.0450, to_date('01-12-2009', 'dd-mm-yyyy'));

What I'm trying to achieve is put date periods from table2 into table1, so it look like this

enter image description here

But I have absolutely no idea how to achieve it. I need four records, Cartesian makes 6, I'm trying with where conditions (and t2.date_From not between t1.date_From and t2.date_From or t1.date_from=t2.date_From) but then t2.date_From is missing.

This is a sample data, SQL which is generating this data is large, so I would like to avoid calling these tables multiple times.

Thanks!

EDIT: I found one way to do this, maybe someone has a better idea, but looks like it works:

SELECT * 
FROM 
    (SELECT 
         t1.date_from,
         t1.date_to,
         t2.percent,
         t2.date_from AS date_from2,
         t2.max_date,
         NVL(GREATEST(t1.date_from, t2.date_From), t1.date_from) AS new_vf,
         NVL(GREATEST(NVL(LEAD(GREATEST(t1.date_from, t2.date_From)) OVER (ORDER BY t1.date_from ASC), t1.date_to), t2.date_From), t1.date_to) AS new_vt
     FROM   
         test1 t1
     LEFT JOIN   
         (SELECT  
              t3.*,
              MAX(date_from) OVER() AS max_Date
          FROM    
              test2 t3) t2 ON t1.id = t2.id
                           AND t2.max_date BETWEEN t1.date_From AND t1.date_to
    ) t
ORDER BY 
    t.date_From ASC, t.date_to ASC
Solution

  • You can use the LEAD analytic function to generate the next date for TEST2 and then join when the date ranges overlap:

    SELECT t1.id,
       GREATEST(t1.date_from, t2.date_from) AS date_from,
       LEAST(t1.date_to, t2.date_to) AS date_to,
       t2.percent
FROM   (
         SELECT id,
                percent,
                date_from,
                LEAD(date_from, 1, DATE '4712-12-31')
                  OVER (PARTITION BY id ORDER BY date_from) AS date_to
         FROM   test2
       ) t2
       INNER JOIN test1 t1
       ON     t1.id        = t2.id
          AND t1.date_from < t2.date_to
          AND t2.date_from < t1.date_to

    Which, for the sample data, outputs:

    ID DATE_FROM DATE_TO PERCENT
    546 2005-12-01 00:00:00 2006-12-01 00:00:00 .01
    546 2006-12-01 00:00:00 2009-12-01 00:00:00 .01
    546 2009-12-01 00:00:00 2011-07-01 00:00:00 .045
    546 2011-07-01 00:00:00 4712-12-31 00:00:00 .045

    fiddle