Oracle 18c

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 (ITEMID, VALIDFROM, VALIDTO) AS
SELECT 1, DATE '2024-01-01', DATE '2024-01-02' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2024-01-04', DATE '2024-01-10' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2024-01-15', null FROM DUAL UNION ALL
SELECT 2, DATE '2023-12-30', DATE '2024-01-02' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, DATE '2023-12-31', null FROM DUAL UNION ALL
SELECT 4, DATE '2023-12-30', DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL;
6 rows affected

SELECT itemid
FROM   table_name
GROUP BY itemid
HAVING COUNT(
         CASE
         WHEN validfrom <= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
         AND  (  validto IS NULL
              OR validto >= DATE '2024-01-03'
              )
         THEN 1
         END
       ) = 0


ITEMID


2

1


SELECT itemid
FROM   table_name
GROUP BY itemid
HAVING COUNT(
         CASE
         WHEN validfrom <= DATE '2024-01-03'
         AND  (  validto IS NULL
              OR validto >= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
              )
         THEN 1
         END
       ) = 0


ITEMID


2

1


SELECT itemid
FROM   table_name
       MATCH_RECOGNIZE(
         -- Merge consecutive ranges into a single range
         PARTITION BY itemid
         ORDER BY validfrom
         MEASURES
           FIRST(validfrom) AS validfrom,
           LAsT(validto) AS validto
         PATTERN (consecutive* last_row)
         DEFINE
           consecutive AS validfrom + INTERVAL '1' SECOND = NEXT(validto)
       )
GROUP BY itemid
HAVING COUNT(
         CASE
         WHEN validfrom <= DATE '2024-01-03'
         AND  (  validto IS NULL
              OR validto >= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
              )
         THEN 1
         END
       ) = 0


ITEMID


1

2


INSERT INTO table_name (ITEMID, VALIDFROM, VALIDTO)
SELECT 5, DATE '2024-01-03' + INTERVAL '1' HOUR, DATE '2024-01-03' + INTERVAL '2' HOUR FROM DUAL UNION ALL
SELECT 6, DATE '2024-01-02', DATE '2024-01-03' + INTERVAL '01:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 6, DATE '2024-01-03' + INTERVAL '2' HOUR, DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL;
3 rows affected

SELECT itemid
FROM   table_name
GROUP BY itemid
HAVING COUNT(
         CASE
         WHEN validfrom <= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
         AND  (  validto IS NULL
              OR validto >= DATE '2024-01-03'
              )
         THEN 1
         END
       ) = 0


ITEMID


2

1


SELECT t.*,
       CASE
       WHEN validfrom <= DATE '2024-01-03'
       AND  (  validto IS NULL
            OR validto >= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
            )
       THEN 1
       END AS match
FROM   table_name t


ITEMID
VALIDFROM
VALIDTO
MATCH


1
2024-01-01 00:00:00
2024-01-02 23:59:59
null

1
2024-01-04 00:00:00
2024-01-10 23:59:59
null

1
2024-01-15 00:00:00
null
null

2
2023-12-30 00:00:00
2024-01-02 23:59:59
null

3
2023-12-31 00:00:00
null
1

4
2023-12-30 00:00:00
2024-01-03 23:59:59
1

5
2024-01-03 01:00:00
2024-01-03 02:00:00
null

6
2024-01-02 00:00:00
2024-01-03 01:59:59
null

6
2024-01-03 02:00:00
2024-01-03 23:59:59
null


SELECT itemid
FROM   table_name
GROUP BY itemid
HAVING COUNT(
         CASE
         WHEN validfrom <= DATE '2024-01-03'
         AND  (  validto IS NULL
              OR validto >= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
              )
         THEN 1
         END
       ) = 0


ITEMID


6

2

5

1


SELECT *
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY itemid
         ORDER BY validfrom
         MEASURES
           FIRST(validfrom) AS validfrom,
           LAsT(validto) AS validto
         PATTERN (consecutive* last_row)
         DEFINE
           consecutive AS validto + INTERVAL '1' SECOND = NEXT(validfrom)
       )


ITEMID
VALIDFROM
VALIDTO


1
2024-01-01 00:00:00
2024-01-02 23:59:59

1
2024-01-04 00:00:00
2024-01-10 23:59:59

1
2024-01-15 00:00:00
null

2
2023-12-30 00:00:00
2024-01-02 23:59:59

3
2023-12-31 00:00:00
null

4
2023-12-30 00:00:00
2024-01-03 23:59:59

5
2024-01-03 01:00:00
2024-01-03 02:00:00

6
2024-01-02 00:00:00
2024-01-03 23:59:59


SELECT itemid
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY itemid
         ORDER BY validfrom
         MEASURES
           FIRST(validfrom) AS validfrom,
           LAsT(validto) AS validto
         PATTERN (consecutive* last_row)
         DEFINE
           consecutive AS validto + INTERVAL '1' SECOND = NEXT(validfrom)
       )
GROUP BY itemid
HAVING COUNT(
         CASE
         WHEN validfrom <= DATE '2024-01-03'
         AND  (  validto IS NULL
              OR validto >= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
              )
         THEN 1
         END
       ) = 0


ITEMID


1

2

5
ITEMID	VALIDFROM	VALIDTO	MATCH
1	2024-01-01 00:00:00	2024-01-02 23:59:59	null
1	2024-01-04 00:00:00	2024-01-10 23:59:59	null
1	2024-01-15 00:00:00	null	null
2	2023-12-30 00:00:00	2024-01-02 23:59:59	null
3	2023-12-31 00:00:00	null	1
4	2023-12-30 00:00:00	2024-01-03 23:59:59	1
5	2024-01-03 01:00:00	2024-01-03 02:00:00	null
6	2024-01-02 00:00:00	2024-01-03 01:59:59	null
6	2024-01-03 02:00:00	2024-01-03 23:59:59	null