[SQL] Selecting the earliest row where a specified date value falls between a date range (two...

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.

# UEISAM INITIAL_REG_DATE ACTIVATION_DATE REG_EXPIRATION_DATE
1 P2LYU1JBH7U9 01/21/2020 08/13/2024 08/09/2025
2 P2LYU1JBH7U9 01/21/2020 08/08/2024 08/06/2025
3 P2LYU1JBH7U9 01/21/2020 08/01/2024 07/31/2025
4 P2LYU1JBH7U9 01/21/2020 07/29/2024 07/25/2025
5 P2LYU1JBH7U9 01/21/2020 07/19/2024 07/17/2025
6 P2LYU1JBH7U9 01/21/2020 07/11/2024 07/09/2025
7 P2LYU1JBH7U9 01/21/2020 01/25/2024 01/22/2025
8 P2LYU1JBH7U9 01/21/2020 01/25/2023 01/23/2024

• 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.

Continue reading...