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;
If you can't define an univoque PARTITION of your data, you will not be able to use MATCH_RECOGNIZE for the task.
Better try using a self JOIN if you sure date intervals are disjoint within (id, code) pairs, if not you will have to merge by partition(id, code) first (that part could be done with MATCH_RECOGNIZE).