SQLTeam.com | Weblogs | Forums

Newbie - Querying data based on dates and comparing it to a different fixed date for each row

I need to be able to get the SUM_RESULT (based on the SUM_DATE) right before the EVENT_DATE (just the one closest to it as it can be any date) and then the first SUM_RESULT at least 12 months from the EVENT_DATE. This is my query so far but I have multiple rows at present for each patient.

SELECT PATIENT_INFO.PATIENT_SURNAME, PATIENT_INFO.PATIENT_GIVEN_NAME, PATIENT_INFO.PATIENT_DOB, PATIENT_INFO.PATIENT_MRN, CLIN_VISITS_SUM.SUM_DATE, CLIN_VISITS_SUM.SUM_RESULT,
CLIN_ANZDATA_EVENT.EVENT_DATE, CLIN_ANZDATA_EVENT.EVENT_DESC, LU_ANZEVENTS_1.ANZEVT_ID, LU_PATVISIT_COL.PATCOL_DESC, CLIN_VISIT_TYPE.visit_date
FROM PATIENT_INFO LEFT OUTER JOIN
CLIN_VISITS_SUM ON PATIENT_INFO.PATIENT_ID = CLIN_VISITS_SUM.PATIENT_ID LEFT OUTER JOIN
CLIN_VISIT_TYPE ON CLIN_VISITS_SUM.PATCOL_ID = CLIN_VISIT_TYPE.PATCOL_ID LEFT OUTER JOIN
LU_PATVISIT_COL ON CLIN_VISITS_SUM.PATCOL_ID = LU_PATVISIT_COL.PATCOL_ID LEFT OUTER JOIN
CLIN_ANZDATA_EVENT ON PATIENT_INFO.PATIENT_ID = CLIN_ANZDATA_EVENT.PATIENT_ID LEFT OUTER JOIN
LU_ANZEVENTS ON CLIN_ANZDATA_EVENT.ANZEVT_SERIES = LU_ANZEVENTS.ANZEVT_SERIES LEFT OUTER JOIN
LU_ANZEVENTS AS LU_ANZEVENTS_1 ON CLIN_ANZDATA_EVENT.ANZEVT_SERIES = LU_ANZEVENTS_1.ANZEVT_SERIES
WHERE (LU_ANZEVENTS_1.ANZEVT_ID = 'G') AND (LU_PATVISIT_COL.PATCOL_DESC = 'eGFR') AND (CLIN_VISITS_SUM.SUM_DATE >= CLIN_ANZDATA_EVENT.EVENT_DATE)

Any help would be greatly appreciated. Thank you

If you supply DDL, Sample Data and expected results, then we can provide better help, otherwise we're just guessing and go back and forth