FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

CREATE TABLE table_name (part, supplier, order_name, order_type, start_date, end_date) AS
select  'part1', 'supplierAAA', 'order1', 'E', to_date('01-01-2023','dd-mm-yyyy'), to_date('31-12-2023','dd-mm-yyyy') from dual union all 
select  'part2', 'supplierBBB', 'order2', 'E', to_date('01-01-2022','dd-mm-yyyy'), to_date('31-12-2022','dd-mm-yyyy') from dual union all 
select  'part2', 'supplierBBB', 'order4', 'G', to_date('01-01-2023','dd-mm-yyyy'), to_date('31-12-2023','dd-mm-yyyy') from dual union all 
select  'part3', 'supplierCCC', 'order5', 'E', to_date('01-01-2022','dd-mm-yyyy'), to_date('30-06-2022','dd-mm-yyyy') from dual union all 
select  'part3', 'supplierCCC', 'order6', 'E', to_date('01-01-2023','dd-mm-yyyy'), to_date('31-12-2023','dd-mm-yyyy') from dual union all 
select  'part3', 'supplierCCC', 'order7', 'G', to_date('01-01-2022','dd-mm-yyyy'), to_date('30-06-2022','dd-mm-yyyy') from dual union all 
select  'part3', 'supplierCCC', 'order8', 'G', to_date('01-01-2023','dd-mm-yyyy'), to_date('15-12-2023','dd-mm-yyyy') from dual union all 
select  'part3', 'supplierCCC', 'order9', 'G', to_date('16-12-2023','dd-mm-yyyy'), to_date('31-12-2024','dd-mm-yyyy') from dual;

WITH g (part, supplier, order_name, start_date, end_date) AS (
  SELECT part,
         supplier,
         order_name,
         start_date,
         end_date
  FROM   table_name 
  WHERE  order_type = 'G'
),
overlapping_g (part, supplier, order_name, start_date, end_date, s_date, e_date) AS (
  SELECT part,
         supplier,
         order_name,
         start_date,
         end_date,
         s_date,
         MAX(end_date) OVER (PARTITION BY part, supplier, mn)
  FROM   g
         MATCH_RECOGNIZE(
           PARTITION BY part, supplier
           ORDER BY start_date, end_date DESC
           MEASURES
             FIRST(start_date) AS s_date,
             MATCH_NUMBER() AS mn
           ALL ROWS PER MATCH
           PATTERN ( overlapping* last_row )
           DEFINE overlapping AS NEXT(start_date) <= MAX(end_date) + INTERVAL '1' DAY
         )
)
SELECT g.part,
       g.supplier,
       g.order_name AS g_order,
       g.start_date AS g_start,
       g.end_date   AS g_end,
       e.order_name AS e_order,
       e.start_date AS e_start,
       e.end_date   AS e_end
FROM   overlapping_g g
       INNER JOIN table_name e
       ON e.part = g.part
          AND e.supplier = g.supplier
          AND g.s_date <= e.start_date
          AND e.end_date <= g.e_date
WHERE  e.order_type = 'E';

SELECT part,
         supplier,
         order_name,
         order_type,
         start_date,
         end_date,
         s_date,
         MAX(end_date) OVER (PARTITION BY part, supplier, order_type, mn) AS e_date
  FROM   table_name
         MATCH_RECOGNIZE(
           PARTITION BY part, supplier, order_type
           ORDER BY start_date, end_date DESC
           MEASURES
             FIRST(start_date) AS s_date,
             MATCH_NUMBER() AS mn
           ALL ROWS PER MATCH
           PATTERN ( overlapping_g* last_g | e )
           DEFINE
             overlapping_g AS  order_type = 'G'
                           AND NEXT(start_date) <= MAX(end_date) + INTERVAL '1' DAY,
             last_g AS order_type = 'G',
             e AS order_type = 'E'
         )
   ORDER BY part, supplier, s_date DESC, order_type, e_date, start_date DESC, end_date;

WITH overlaps (part, supplier, order_name, order_type, start_date, end_date, s_date, e_date) AS (
  SELECT part,
         supplier,
         order_name,
         order_type,
         start_date,
         end_date,
         s_date,
         MAX(end_date) OVER (PARTITION BY part, supplier, order_type, mn)
  FROM   table_name
         MATCH_RECOGNIZE(
           PARTITION BY part, supplier, order_type
           ORDER BY start_date, end_date DESC
           MEASURES
             FIRST(start_date) AS s_date,
             MATCH_NUMBER() AS mn
           ALL ROWS PER MATCH
           PATTERN ( overlapping_g* last_g | e )
           DEFINE
             overlapping_g AS  order_type = 'G'
                           AND NEXT(start_date) <= MAX(end_date) + INTERVAL '1' DAY,
             last_g AS order_type = 'G',
             e AS order_type = 'E'
         )
 )
 SELECT *
 FROM   overlaps
        MATCH_RECOGNIZE(
          PARTITION BY part, supplier
          ORDER BY s_date DESC, order_type, e_date, start_date DESC, end_date
          ALL ROWS PER MATCH
          AFTER MATCH SKIP TO NEXT ROW
          PATTERN (e ( {-any_row*? -} g)+)
          DEFINE
            e AS order_type = 'E',
            g AS order_type = 'G'
              AND e.e_date <= g.e_date
        );

PART	SUPPLIER	G_ORDE	G_START	G_END	E_ORDE	E_START	E_END
part3	supplierCCC	order7	2022-01-01 00:00:00	2022-06-30 00:00:00	order5	2022-01-01 00:00:00	2022-06-30 00:00:00
part3	supplierCCC	order8	2023-01-01 00:00:00	2023-12-15 00:00:00	order6	2023-01-01 00:00:00	2023-12-31 00:00:00
part3	supplierCCC	order9	2023-12-16 00:00:00	2024-12-31 00:00:00	order6	2023-01-01 00:00:00	2023-12-31 00:00:00

PART	SUPPLIER	ORDER_	O	START_DATE	END_DATE	S_DATE	E_DATE
part1	supplierAAA	order1	E	2023-01-01 00:00:00	2023-12-31 00:00:00	2023-01-01 00:00:00	2023-12-31 00:00:00
part2	supplierBBB	order4	G	2023-01-01 00:00:00	2023-12-31 00:00:00	2023-01-01 00:00:00	2023-12-31 00:00:00
part2	supplierBBB	order2	E	2022-01-01 00:00:00	2022-12-31 00:00:00	2022-01-01 00:00:00	2022-12-31 00:00:00
part3	supplierCCC	order6	E	2023-01-01 00:00:00	2023-12-31 00:00:00	2023-01-01 00:00:00	2023-12-31 00:00:00
part3	supplierCCC	order9	G	2023-12-16 00:00:00	2024-12-31 00:00:00	2023-01-01 00:00:00	2024-12-31 00:00:00
part3	supplierCCC	order8	G	2023-01-01 00:00:00	2023-12-15 00:00:00	2023-01-01 00:00:00	2024-12-31 00:00:00
part3	supplierCCC	order5	E	2022-01-01 00:00:00	2022-06-30 00:00:00	2022-01-01 00:00:00	2022-06-30 00:00:00
part3	supplierCCC	order7	G	2022-01-01 00:00:00	2022-06-30 00:00:00	2022-01-01 00:00:00	2022-06-30 00:00:00

PART	SUPPLIER	S_DATE	O	E_DATE	START_DATE	END_DATE	ORDER_
part3	supplierCCC	2023-01-01 00:00:00	E	2023-12-31 00:00:00	2023-01-01 00:00:00	2023-12-31 00:00:00	order6
part3	supplierCCC	2023-01-01 00:00:00	G	2024-12-31 00:00:00	2023-12-16 00:00:00	2024-12-31 00:00:00	order9
part3	supplierCCC	2023-01-01 00:00:00	G	2024-12-31 00:00:00	2023-01-01 00:00:00	2023-12-15 00:00:00	order8
part3	supplierCCC	2022-01-01 00:00:00	E	2022-06-30 00:00:00	2022-01-01 00:00:00	2022-06-30 00:00:00	order5
part3	supplierCCC	2022-01-01 00:00:00	G	2022-06-30 00:00:00	2022-01-01 00:00:00	2022-06-30 00:00:00	order7