0

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;
astentx
  • 6,393
  • 2
  • 16
  • 25
MichalAndrzej
  • 77
  • 1
  • 11
  • Please [edit] the question to explain the logic you want to use in greater depth and include alternate test cases which help to demonstrate the logic you are applying and the expected output you expect for those test cases. If your existing code is already returning the correct data then why do you want to filter it more? – MT0 Sep 07 '22 at 08:01
  • I've added alternative cases. No, my actual code isn't returning correct data now - it's only returning final results per code (which is previous step), next step is to mix values with different codes – MichalAndrzej Sep 07 '22 at 08:38
  • @MichalAndrzej so if you have 10 rows for code X and 10 rows for code Y and each end every of them overlaps with each other then result will have 100 rows, right? Similarly if there is 10 rows for codes X, Y, Z (30 input rows) such as each row overlaps with all others then result will have 100 + 100 + 100 = 300 rows, correct? – Dr Y Wit Sep 07 '22 at 09:36
  • the most trivial and most efficient solution is self join `select t1.code code1, t2.code code2, greatest(t1.date_from, t2.date_from) date_from, least(t1.date_to, t2.date_to) date_to from t t1 join t t2 on t1.date_to > t2.date_from and t1.date_from < t1.date_to and t1.code < t2.code`; it can be solved with pattern matching instead but that is much less efficient for this task. – Dr Y Wit Sep 07 '22 at 09:44

1 Answers1

0

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).

p3consulting
  • 2,721
  • 2
  • 12
  • 10