Matching test dates to first entrance date

harishgg1 · May 9, 2026, 9:00am

hi

hope this helps

solved = do not believe so

SELECT 
    ps.IdentityID,
    ps.EntranceDate,
    ps.ExitDate,
    (SELECT MIN(t1.Test1Date)
     FROM Test1 t1
     WHERE t1.IdentityID = ps.IdentityID
       AND t1.Test1Date > ps.EntranceDate) AS Test1Date,
    (SELECT MIN(t2.Test2Date)
     FROM Test2 t2
     WHERE t2.IdentityID = ps.IdentityID
       AND t2.Test2Date > ps.EntranceDate) AS Test2Date
FROM PatientStays ps
WHERE ps.EntranceDate = (
    SELECT MIN(p2.EntranceDate)
    FROM PatientStays p2
    WHERE p2.IdentityID = ps.IdentityID
      AND p2.EntranceDate BETWEEN '2015-07-01' AND '2023-12-31'
);