I have two sub-queries to get visit date and visit type that I combined into one outer apply. The sub-queries for visit date and visit type give me blanks when there is no visit, which is what I want. The outer apply gives me blanks for visit type but for date give me 01/01/1900.
I can solve the issue with a
where fldvisitdate <> '19000101'
but I'm wondering why the outer apply is working this way.
here is the code of my two sub-queries.
(SELECT TOP 1 fldVisitDate FROM tblVisit inner join tblschedule on tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid WHERE fldVisitPrerepID = tblRunnerPrerepIDs.fldPrerepID and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid ORDER BY fldVisitDate) AS 'First Visit', (SELECT TOP 1 tblVisitTypes.fldVisitTypeName FROM tblVisitTypes inner join tblVisit ON tblVisit.fldVisitTypeID = tblVisitTypes.fldVisitTypeID inner join tblschedule on tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid WHERE fldVisitPrerepIDID = tblRunnerPrerepIDs.fldPrerepID and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid ORDER BY fldVisitDate) as 'First Visit Type', OUTER APPLY (SELECT TOP 1 tblVisit.fldVisitDate, tblVisit.fldVisitDate, tblVisitTypes.fldVisitTypeName FROM tblVisit inner join tblVisitTypes ON tblVisit.fldVisitTypeID = tblVisitTypes.fldVisitTypeID inner join tblSchedule on tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid WHERE fldVisitPrerepID = tblRunnerPrereps.fldPrerepID and tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid ORDER BY fldVisitDate) as FirstVisitData
Of course earlier in the query I request a display of
FirstVisitData.fldvisitdate as ‘First Visit’ FirstVisitData.fldVisitTypeName as ‘First Visit Type’