I have problem with divide dates into correct periods. Here's an example:
id | count | code | date_from | date_to |
---|---|---|---|---|
4364 | 2 | 201 | 01/08/2022 15:00:00 | 10/09/2022 22:00:00 |
4364 | 2 | 201 | 13/09/2022 05:20:00 | 30/09/2022 17:00:00 |
4364 | 2 | 4013 | 29/08/2022 04:48:00 | 19/11/2022 13:43:00 |
My goal is to get common periods for those dates, so in this case it will be:
Expected result:
29/08/2022 15:00 - 10/09/2022 22:00
13/09/2022 05:20 - 30/09/2022 17:00
Alternative cases:
IF
201 - the same two rows
4013 - 10/08/2022 - 08/09/2022
THEN EXPECTED RESULT:
10/08/2022 - 08/09/2022
13/09/2022 - 30/09/2022
IF
201 - the same two rows
4013 - 15/09/2022 - 22/09/2022
THEN EXPECTED RESULT:
01/08/2022 - 10/09/2022
15/09/2022 - 22/09/2022
IF
201 - the same two rows
4013 - 11/09/2022 - 12/09/2022
THEN EXPECTED RESULT:
NULL (zero rows)
The tricky part is that periods are already returned correctly per 'code' (that's why 201 has two periods), so I need to combine different codes somehow
I was using something like this (but it doesn't work correctly if 'code 4013' is overlapping both dates of 201)
SELECT *
FROM table_gtt
MATCH_RECOGNIZE (PARTITION BY id
ORDER BY date_from, date_to
MEASURES
MIN(date_from) date_from
,MAX(date_to) date_to
PATTERN (overlap* last_row)
DEFINE
overlap AS MAX(date_to) >= NEXT(date_from)
)
Can anyone has idea how to solve this?
Data:
CREATE GLOBAL TEMPORARY TABLE table_gtt
ON COMMIT PRESERVE ROWS
AS
select 4364 id, 2 count, 201 code, TO_DATE('01/08/2022 15:00', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('10/09/2022 22:00', 'DD/MM/YYYY HH24:MI') date_to from dual
union all
select 4364 id, 2 count, 201 code, TO_DATE('13/09/2022 05:20', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('30/09/2022 17:00', 'DD/MM/YYYY HH24:MI') date_to from dual
union all
select 4364 id, 2 count, 4013 code, TO_DATE('29/08/2022 04:48', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('19/11/2022 13:43', 'DD/MM/YYYY HH24:MI') date_to from dual;