By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table1 (st1, ed1) AS
SELECT DATE '2020-06-05', DATE '2020-08-15' FROM DUAL UNION ALL
SELECT DATE '2020-09-01', DATE '2020-09-15' FROM DUAL;
2 rows affected
CREATE TABLE table2 (st2, ed2) AS
SELECT DATE '2020-05-01', DATE '2020-06-10' FROM DUAL UNION ALL
SELECT DATE '2020-07-01', DATE '2020-07-03' FROM DUAL UNION ALL
SELECT DATE '2020-08-01', DATE '2020-08-13' FROM DUAL;
3 rows affected
SELECT dt as st,
LEAD(dt) OVER (ORDER BY dt) - CASE LEAD(type) OVER (ORDER BY dt) WHEN 1 THEN 1 ELSE 0 END AS ed,
SUM(CASE WHEN table_type = 1 THEN type ELSE 0 END) OVER (ORDER BY dt) AS num_range1,
SUM(CASE WHEN table_type = 2 THEN type ELSE 0 END) OVER (ORDER BY dt) AS num_range2,
type,
table_type
FROM (
SELECT dt, type, 1 AS table_type
FROM table1
UNPIVOT (dt FOR type IN (st1 AS 1, ed1 As -1))
UNION ALL
SELECT dt, type, 2
FROM table2
UNPIVOT (dt FOR type IN (st2 AS 1, ed2 As -1))
ORDER BY dt
)
| ST | ED | NUM_RANGE1 | NUM_RANGE2 | TYPE | TABLE_TYPE |
|---|---|---|---|---|---|
| 2020-05-01 00:00:00 | 2020-06-04 00:00:00 | 0 | 1 | 1 | 2 |
| 2020-06-05 00:00:00 | 2020-06-10 00:00:00 | 1 | 1 | 1 | 1 |
| 2020-06-10 00:00:00 | 2020-06-30 00:00:00 | 1 | 0 | -1 | 2 |
| 2020-07-01 00:00:00 | 2020-07-03 00:00:00 | 1 | 1 | 1 | 2 |
| 2020-07-03 00:00:00 | 2020-07-31 00:00:00 | 1 | 0 | -1 | 2 |
| 2020-08-01 00:00:00 | 2020-08-13 00:00:00 | 1 | 1 | 1 | 2 |
| 2020-08-13 00:00:00 | 2020-08-15 00:00:00 | 1 | 0 | -1 | 2 |
| 2020-08-15 00:00:00 | 2020-08-31 00:00:00 | 0 | 0 | -1 | 1 |
| 2020-09-01 00:00:00 | 2020-09-15 00:00:00 | 1 | 0 | 1 | 1 |
| 2020-09-15 00:00:00 | null | 0 | 0 | -1 | 1 |
SELECT st, ed
FROM (
SELECT dt as st,
LEAD(dt) OVER (ORDER BY dt) - CASE LEAD(type) OVER (ORDER BY dt) WHEN 1 THEN 1 ELSE 0 END AS ed,
SUM(CASE WHEN table_type = 1 THEN type ELSE 0 END) OVER (ORDER BY dt) AS num_range1,
SUM(CASE WHEN table_type = 2 THEN type ELSE 0 END) OVER (ORDER BY dt) AS num_range2
FROM (
SELECT dt, type, 1 AS table_type
FROM table1
UNPIVOT (dt FOR type IN (st1 AS 1, ed1 As -1))
UNION ALL
SELECT dt, type, 2
FROM table2
UNPIVOT (dt FOR type IN (st2 AS 1, ed2 As -1))
ORDER BY dt
)
)
WHERE num_range1 > 0
AND num_range2 = 0;
| ST | ED |
|---|---|
| 2020-06-10 00:00:00 | 2020-06-30 00:00:00 |
| 2020-07-03 00:00:00 | 2020-07-31 00:00:00 |
| 2020-08-13 00:00:00 | 2020-08-15 00:00:00 |
| 2020-09-01 00:00:00 | 2020-09-15 00:00:00 |