exclude orders based on time conditions and overlapping value in SQL Oracle

Question

I posted this requirent in the past and the answer is correct. I would like to extend it with anothe conditions that could not work with this solution.

exclude specific rows based on time conditions in SQL Oracle

I modified the table a bit. with the above solution all 4 rows are set to PASS

CREATE TABLE table_name (brand, type, start_date, end_date) AS
  SELECT 'abc', 'W', DATE '2020-01-01', DATE '2020-06-30' FROM DUAL UNION ALL
  SELECT 'abc', 'W', DATE '2020-07-01', DATE '2020-08-31' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2019-01-01', DATE '2022-09-30' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2019-01-01', DATE '2020-03-31' FROM DUAL

SQL

WITH continuous_w_ranges (brand, start_date, end_date) AS (
  SELECT brand, start_date, end_date
  FROM   (
    SELECT *
    FROM   table_name
    WHERE type = 'W'
  )
  MATCH_RECOGNIZE(
    PARTITION BY brand
    ORDER BY start_date, end_date
    MEASURES
      FIRST(start_date) AS start_date,
      MAX(end_date)     AS end_date
    PATTERN ( continuing_range* next_range )
    DEFINE
      continuing_range AS MAX(end_date) + 1 >= NEXT(start_date) 
  )
)
SELECT brand,
       type,
       start_date,
       end_date,
       CASE
       WHEN type = 'A'
       AND  EXISTS(
              SELECT 1
              FROM   continuous_w_ranges w
              WHERE  w.brand = a.brand
              AND    w.start_date <= a.start_date
              AND    a.end_date <= w.end_date
            )
       THEN 'not pass'
       ELSE 'pass'
       END AS pass
FROM   table_name a
ORDER BY brand, start_date;

Now I want to test if a specific date overlaps with both types. for example a date 01-01-2020. The order with type A should be marked as PASS only if this specific date is crossing the orders of type A only! In this case orders of type W should be marked as PASS as before but order of type A as NOT PASS. If I used a date 01.10.2019 then all order will be marked as PASS If I used a date 01.10.2021 then the last order will be as NOT PASS and the other 2 orders as PASS.

order of type W will be always as PASS but orders of type A just in case they do not overlap with at least one order of type W on this specific date.

Answer 1

order of type W will be always as PASS but orders of type A just in case they do not overlap with at least one order of type W on this specific date.

Assuming that your final sentence is a complete summary of the logic that you want to implement (and that the question is self-contained and there is no logic described outside the question that also needs to be implemented).

You can use the analytic COUNT function and conditional aggregation.

SELECT t.*,
       CASE
       WHEN type = 'W'
       OR   (   COUNT(
                  CASE
                  WHEN type = 'A'
                  AND  :input_date BETWEEN start_date AND end_date
                  THEN 1
                  END
                ) OVER (PARTITION BY brand) > 0
            AND COUNT(
                  CASE
                  WHEN type = 'W'
                  AND  :input_date BETWEEN start_date AND end_date
                  THEN 1
                  END
                ) OVER (PARTITION BY brand) = 0
            )
       THEN 'PASS'
       ELSE 'FAIL'
       END AS pass
FROM   table_name t

So, if the :input_date is 2020-01-01 then, for your sample data, the output is:

BRAND	TYPE	START_DATE	END_DATE	PASS
abc	W	2020-01-01 00:00:00	2020-06-30 00:00:00	PASS
abc	A	2019-01-01 00:00:00	2020-03-31 00:00:00	FAIL
abc	A	2019-01-01 00:00:00	2022-09-30 00:00:00	FAIL
abc	W	2020-07-01 00:00:00	2020-08-31 00:00:00	PASS

and W rows will always pass and A rows will pass if there is at least one row that overlaps that date when the type is A and there are no W rows that overlap that date; so, in this case, the A rows fail.

and if :input_date is 2019-12-31 then the output is:

BRAND	TYPE	START_DATE	END_DATE	PASS
abc	W	2020-01-01 00:00:00	2020-06-30 00:00:00	PASS
abc	A	2019-01-01 00:00:00	2020-03-31 00:00:00	PASS
abc	A	2019-01-01 00:00:00	2022-09-30 00:00:00	PASS
abc	W	2020-07-01 00:00:00	2020-08-31 00:00:00	PASS

and the A rows pass.

fiddle