Oracle 23c

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

create table Point_of_sale (Brand, Sub_Brand, Value, START_Date, End_Date) AS
SELECT 'Silverware', 'Spoon', 10, DATE '2020-01-01', DATE '2020-12-20' FROM DUAL UNION ALL
SELECT 'Silverware', 'Spoon', 20, DATE '2021-01-01', NULL FROM DUAL UNION ALL
SELECT 'Silverware', 'Spoon', 30, DATE '2021-05-05', NULL FROM DUAL;
3 rows affected

SELECT *
FROM   (
  SELECT p.*,
         COUNT(CASE WHEN end_date IS NULL THEN 1 END)
           OVER (PARTITION BY brand, sub_brand) AS num_null
  FROM   point_of_sale p
)
WHERE  num_null > 1
AND    end_date IS NULL;


BRAND
SUB_BRAND
VALUE
START_DATE
END_DATE
NUM_NULL


Silverware
Spoon
30
05-MAY-21
null
2

Silverware
Spoon
20
01-JAN-21
null
2


SELECT *
FROM   (
  SELECT p.*,
         COUNT(CASE WHEN end_date IS NULL THEN 1 END)
           OVER (PARTITION BY brand, sub_brand) AS num_null
  FROM   point_of_sale p
  WHERE  start_date <= SYSDATE
)
WHERE  num_null > 1
AND    end_date IS NULL;


BRAND
SUB_BRAND
VALUE
START_DATE
END_DATE
NUM_NULL


Silverware
Spoon
30
05-MAY-21
null
2

Silverware
Spoon
20
01-JAN-21
null
2

BRAND	SUB_BRAND	VALUE	START_DATE	END_DATE	NUM_NULL
Silverware	Spoon	30	05-MAY-21	null	2
Silverware	Spoon	20	01-JAN-21	null	2