How to use WHEN EXISTS inside a CASE Statement

Question

Post reply

How to use WHEN EXISTS inside a CASE Statement

J39L4753

SSCrazy

Points: 2803
More actions
May 8, 2012 at 3:55 am

#260530

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

vinu512 SSCoach Points: 15729 More actions · Answer 1

Please post DDL of the tables and some sample data.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Yes, just do:

SELECT CASE WHEN EXISTS(subquery) THEN...

There are some situations you can't use it (e.g. in a group by clause IIRC), but SQL should tell you quite clearly in that situation.

Thanks

J39L4753 SSCrazy Points: 2803 More actions · Answer 3

Gazareth (5/8/2012)
Yes, just do:
SELECT CASE WHEN EXISTS(subquery) THEN...
There are some situations you can't use it (e.g. in a group by clause IIRC), but SQL should tell you quite clearly in that situation.
Thanks

Thanks 🙂 I'll give that a go