Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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
4 rows affected

SELECT t.*,
       CASE
       WHEN type = 'W'
       OR   (   COUNT(
                  CASE
                  WHEN type = 'A'
                  AND  DATE '2020-01-01' BETWEEN start_date AND end_date
                  THEN 1
                  END
                ) OVER (PARTITION BY brand) > 0
            AND COUNT(
                  CASE
                  WHEN type = 'W'
                  AND  DATE '2020-01-01' 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


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


SELECT t.*,
       CASE
       WHEN type = 'W'
       OR   (   COUNT(
                  CASE
                  WHEN type = 'A'
                  AND  DATE '2019-12-31' BETWEEN start_date AND end_date
                  THEN 1
                  END
                ) OVER (PARTITION BY brand) > 0
            AND COUNT(
                  CASE
                  WHEN type = 'W'
                  AND  DATE '2019-12-31' 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


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

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