Selecting the earliest row where a specified date value falls between a date range (two columns) when multiple rows may match

I am working with CONTRACT data (Start Dates) and SAM data (Entity Registrations) to find the earliest row of SAM registration data what was active when the contract started.

• So if my CONTRACT Start Date was '01-FEB-2024' I would just want row #7
• if my CONTRACT Start Date was '15-JUL-2024' I would just want row #6
• if my CONTRACT Start Date was '01-AUG-2024' I would just want row #3

The following is where I am starting but I will get multiple row when running this using the three contractstartdate(x) values in my CTE.

  WITH CONTRACTS AS ( SELECT 'P2LYU1JBH7U9' AS SAM
                             ,TO_DATE('01-FEB-2024') AS CONTRACTSTARTDATE1
                             ,TO_DATE('15-JUL-2024') AS CONTRACTSTARTDATE2
                             ,TO_DATE('01-AUG-2024') AS CONTRACTSTARTDATE3
                        FROM DUAL)
      SELECT S.UEISAM
           , TRUNC(S.INITIAL_REGISTRATION_DATE)      AS INITIAL_REGISTRATION_DATE
           , TRUNC(S.ACTIVATION_DATE)                AS ACTIVATION_DATE
           , TRUNC(S.REGISTRATION_EXPIRATION_DATE)   AS REGISTRATION_EXPIRATION_DATE
           , s.*
        FROM OC_ARB_SAM_SUP_EXTRACTS S
   LEFT JOIN CONTRACTS C ON C.SAM = S.UEISAM
       WHERE UEISAM = 'P2LYU1JBH7U9'
--         AND (CONTRACTSTARTDATE3 between S.ACTIVATION_DATE AND S.REGISTRATION_EXPIRATION_DATE)
    ORDER BY S.ACTIVATION_DATE DESC, S.REGISTRATION_EXPIRATION_DATE

When an entity (UEISAM) has multiple rows of data, I want to earliest row with an activation date >= to start date of a given contract.

I want to earliest row with an activation date >= to start date of a given contract.

Your expected output appears to the the latest, not the earliest.

From Oracle 12, you can use a LATERAL join and FETCH FIRST ROW ONLY:

WITH CONTRACTS (sam, contract_start_date) AS (
  SELECT 'P2LYU1JBH7U9', DATE '2024-02-01' FROM DUAL UNION ALL
  SELECT 'P2LYU1JBH7U9', DATE '2024-07-15' FROM DUAL UNION ALL
  SELECT 'P2LYU1JBH7U9', DATE '2024-08-01' FROM DUAL
)
SELECT c.contract_start_date,
       s.*
FROM   CONTRACTS C
       CROSS JOIN LATERAL (
         SELECT *
         FROM   OC_ARB_SAM_SUP_EXTRACTS S
         WHERE  C.SAM = S.UEISAM
         AND    s.activation_date <= C.contract_start_date
         AND    s.reg_expiration_date >= C.contract_start_date
         ORDER BY s.activation_date DESC
         FETCH FIRST ROW ONLY
       ) s

Which, for the sample data:

CREATE TABLE OC_ARB_SAM_SUP_EXTRACTS (RN, UEISAM, INITIAL_REG_DATE, ACTIVATION_DATE, REG_EXPIRATION_DATE) AS
SELECT 1, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2024-08-13', DATE '2025-08-09' FROM DUAL UNION ALL
SELECT 2, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2024-08-08', DATE '2025-08-06' FROM DUAL UNION ALL
SELECT 3, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2024-08-01', DATE '2025-07-31' FROM DUAL UNION ALL
SELECT 4, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2024-07-29', DATE '2025-07-25' FROM DUAL UNION ALL
SELECT 5, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2024-07-19', DATE '2025-07-17' FROM DUAL UNION ALL
SELECT 6, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2024-07-11', DATE '2025-07-09' FROM DUAL UNION ALL
SELECT 7, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2024-01-25', DATE '2025-01-22' FROM DUAL UNION ALL
SELECT 8, 'P2LYU1JBH7U9', DATE '2020-01-21', DATE '2023-01-25', DATE '2024-01-23' FROM DUAL;

Outputs:

fiddle