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 |
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:
| CONTRACT_START_DATE | RN | UEISAM | INITIAL_REG_DATE | ACTIVATION_DATE | REG_EXPIRATION_DATE |
|---|---|---|---|---|---|
| 2024-02-01 00:00:00 | 7 | P2LYU1JBH7U9 | 2020-01-21 00:00:00 | 2024-01-25 00:00:00 | 2025-01-22 00:00:00 |
| 2024-07-15 00:00:00 | 6 | P2LYU1JBH7U9 | 2020-01-21 00:00:00 | 2024-07-11 00:00:00 | 2025-07-09 00:00:00 |
| 2024-08-01 00:00:00 | 3 | P2LYU1JBH7U9 | 2020-01-21 00:00:00 | 2024-08-01 00:00:00 | 2025-07-31 00:00:00 |