Intersection of date ranges across rows in oracle.
I have a table which contains following records




Item_no
item_type
active_from
active_to
rule_id




10001
SAR
2020-01-01
2023-01-01
rule1


10001
SAR.
2024-01-01
9999-12-31
rule1


10001
SAR
2020-05-01
2021-06-01
rule2


10001
SAR
2021-01-01
2021-02-01
rule2




We need to find common dates between rule ids
Output will be




Item_no
item_type
active_from
active_to




10001
SAR
2020-05-01
2021-06-01




I tried with connect by level to generate dates and then take intersection, but it is running for long time due to 9999-12-31

intersection across date ranges from multiple rows in oracle
From Oracle 12, you can UNPIVOT the dates and then use analytic functions and MATCH_RECOGNIZE to process the result set row-by-row to find the consecutive rows where both rules are active:
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
)

Which, for the sample data:
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;

Outputs:




ITEM_NO
ITEM_TYPE
ACTIVE_FROM
ACTIVE_TO




10001
SAR
2020-05-01 00:00:00
2021-06-01 00:00:00




and for:
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;

The output is:




ITEM_NO
ITEM_TYPE
ACTIVE_FROM
ACTIVE_TO




10001
SPR
2023-01-31 00:00:00
2023-01-31 00:00:00




fiddle
