How is it possible to use WHEN EXISTS inside a CASE Statement?
Currently I am using SELECT TOP 1 as per code below but the query is taking some time to run and wonder how it was possible to use the WHEN EXISTS function.
CASE
WHEN
(
SELECT TOP 1 ApptDate
FROM AllApptStatus
WHERE Apptdate + ApptTime >= GETDATE()
AND aReferralID = cr.id
AND ApptStatus <> -568
AND ApptType IS NOT NULL
ORDER BY ApptDate
) IS NOT NULL AND
(
SELECT TOP 1 ApptDate
FROM AllApptstatus
WHERE aReferralID = cr.id
AND ApptStatus IN (-1407, -1408)
ORDER BY ApptDate DESC
) IS NULL THEN 'Future Appt Booked, No Previous'
WHEN
(
SELECT TOP 1 ApptDate
FROM AllApptStatus
WHERE aReferralID = cr.id
AND ApptDate + ApptTime >= GETDATE()
AND ApptStatus <> -568
AND ApptType IS NOT NULL
ORDER BY ApptDate
) IS NOT NULL THEN 'Future Appt Booked, Previous Attended'
WHEN cr.id IN (SELECT iReferralID FROM Investigations) THEN 'Waiting List'
WHEN
(
SELECT TOP 1 ApptDate
FROM AllApptStatus
WHERE aReferralID = cr.id
AND ApptStatus IN (-1407, -1408)
ORDER BY ApptDate DESC
) IS NOT NULL THEN 'Previous Attended, No Future'
ELSE 'Nothing Attended, Nothing Booked'
END ReportStatus
How is it possible to do?
Thanks
