Another way using CROSS-APPLY
SELECTD.UnitNumber, D.IDXMRN, D.DischargeDate, App.ApptDt2, DATEDIFF( DAY, D.DischargeDate, App.ApptDt2 ) AS Days_Difference
FROMDischarges AS D
OUTER APPLY(
SELECTTOP 1 *
FROMAppointments AS A
WHERED.UnitNumber = A.UnitNumber AND D.IDXMRN = A.IDXMRN
ANDD.DischargeDate < A.ApptDt2
) AS App
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/