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 table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SAR', DATE '2020-01-01', DATE '2023-01-01', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2024-01-01', DATE '9999-12-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2020-05-01', DATE '2021-06-01', 'rule2' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2021-01-01', DATE '2021-02-01', 'rule2' FROM DUAL;
4 rows affected
SELECT *
FROM (
SELECT item_no,
item_type,
rule_id,
dt,
SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
) AS rule1,
SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
) AS rule2
FROM table_name
UNPIVOT (
dt FOR active IN ( active_from AS 1, active_to AS -1 )
)
)
MATCH_RECOGNIZE(
PARTITION BY item_no, item_type
ORDER BY dt, rule1 DESC, rule2 DESC
MEASURES
FIRST(dt) AS active_from,
NEXT(dt) AS active_to
PATTERN ( active_rules+ )
DEFINE active_rules AS rule1 > 0 AND rule2 > 0
)
| ITEM_NO | ITEM_TYPE | ACTIVE_FROM | ACTIVE_TO |
|---|---|---|---|
| 10001 | SAR | 2020-05-01 00:00:00 | 2021-06-01 00:00:00 |
DROP TABLE table_name;
CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SPR', DATE '2023-01-01', DATE '2023-01-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SPR', DATE '2023-01-31', DATE '2023-02-27', 'rule2' FROM DUAL;
2 rows affected
SELECT *
FROM (
SELECT item_no,
item_type,
rule_id,
dt,
SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
) AS rule1,
SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
) AS rule2
FROM table_name
UNPIVOT (
dt FOR active IN ( active_from AS 1, active_to AS -1 )
)
)
MATCH_RECOGNIZE(
PARTITION BY item_no, item_type
ORDER BY dt, rule1 DESC, rule2 DESC
MEASURES
FIRST(dt) AS active_from,
NEXT(dt) AS active_to
PATTERN ( active_rules+ )
DEFINE active_rules AS rule1 > 0 AND rule2 > 0
)
| ITEM_NO | ITEM_TYPE | ACTIVE_FROM | ACTIVE_TO |
|---|---|---|---|
| 10001 | SPR | 2023-01-31 00:00:00 | 2023-01-31 00:00:00 |