Missing date handling between subquery and OUTER APPLY

Question

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’

• Duffy: Is the fldVisitDate stored in an nvarchar or datetime column?
• ypercubeᵀᴹ: Let us continue this discussion in chat.

Answer 1

• First, the 2 original subqueries have different correlation conditions. The first one has and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid (which is a redundant repeat of the ON condition) but the 2nd has and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid which is a correlated condition to the main query.

  So, the two subqueries may have been giving you NULL in some results but they are incorrect (one of them!) if they are supposed to give you related results.

• Second and based on the (broken) results from the OUTER APPLY where the conditions from the first subquery is used, we can deduct that the 2nd subquery has the correct/wanted conditions.

• Other points of trouble is the random formatting of the code, the mixing of upper and lower case SQL keywords and the lack of aliases which makes the code rather unreadable. The more the code in nicely formatted, the easier would be to catch similar errors.

• Columns and table aliases should be quoted (if needed) with double quotes ("some alias") or square brackets ([some alias]) and not with single quotes. While this is allowed for column aliases, it is very confusing and error-prone, as single quotes are used for string literals.

• Columns should always (unless in ORDER BY) be prefixed with the table name or alias. This is mostly fine in the code, except for one miss.

Based on the above, I'd rewrite the OUTER APPLY like this:

OUTER APPLY 
 ( SELECT TOP 1
 v . fldVisitDate, 
 vt . fldVisitTypeName
 FROM tblVisit AS v
 LEFT JOIN tblVisitTypes AS vt
 ON v.fldVisitTypeID = vt.fldVisitTypeID
 INNER JOIN tblSchedule AS s
 ON s.fldScheduleVisitID = v.fldvisitid
 WHERE 
 -- replace "xxx" with table name or alias
 xxx.fldVisitPrerepID = tblRunnerPrereps.fldPrerepID
 AND 
 s.fldScheduleVisitID = tblPatientVisit.fldvisitid
 ORDER BY 
 fldVisitDate
 ) 
 AS FirstVisitData

and then double and triple check that the joining conditions are correct and matching business logic and the intention of the query.

All the above may still not resolve the weird results (1900年01月01日) you get. It may be that they are caused due to the changed conditions (in which case, the rewrite will resolve it) or as @Duffy has commented they may due to some implicit conversion. The empty string '' when converted to DATE becomes by default 1900-Jan-01. A UNION can have this effect. Try this for example and you'll get the 2nd row as 1900-Jan-01:

SELECT getdate() AS SomeDate 
UNION
SELECT '' ;

Without knowing what transformations are happening in the external query, we are not sure about that either nor why this happens only with the OUTER APPLY but not with the subqueries method.