Here's the goal:
CASE
WHEN @OutPatient>0 THEN 'Outpatient Therapy'
WHEN @OutPatient=0 and @MedCheck>0 THEN 'Outpatient Medical Services'
WHEN @OutPatient=0 and @MedCheck=0 and @PsychRehab >0 THEN 'Psychiatric Rehab Services'
END as Column1
Here's how it looks in it's working state:
SELECT A.cl_id,
A.cl_ln,
A.cl_fn,
CASE
WHEN ((SELECT COUNT(*) AS OutCount FROM eventstb E WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 110 AND 113 OR
e_service BETWEEN 180 AND 250 OR
e_service = 280) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))>0) THEN 'Outpatient Therapy'
WHEN ((SELECT COUNT(*) AS OutCount2 FROM eventstb E WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 110 AND 113 OR
e_service BETWEEN 180 AND 250 OR
e_service = 280) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))=0 and
(SELECT COUNT(*) AS OutCount3
FROM eventstb E
WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 400 AND 402) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))>0)
THEN 'Outpatient Medical Services'
WHEN ((SELECT COUNT(*) AS OutCount4 FROM eventstb E WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 110 AND 113 OR
e_service BETWEEN 180 AND 250 OR
e_service = 280) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))=0 and
(SELECT COUNT(*) AS OutCount5
FROM eventstb E
WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 400 AND 402) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))=0 and
(SELECT COUNT(*) AS OutCount6
FROM eventstb E
WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 600 AND 609 OR
e_service BETWEEN 640 AND 685) AND
(e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))>0)
THEN 'Psychiatric Rehab Services'
END as Column1
FROM CLIENTSTB A
WHERE (A.cl_type = 'AC') AND (A.cl_regis = '01') AND (cl_adm_dt < CONVERT(DATETIME, '2007-07-01 00:00:00', 102)) AND
EXISTS(SELECT L.cl_ly_id, L.cl_ly_el_id_1 FROM layercl L
WHERE A.cl_id = L.cl_ly_id and (L.cl_ly_el_la_dt IS NULL) AND (L.cl_ly_fs IN (301, 302)))
ORDER by cl_id ASC
This part of a larger query, but I cut it down to the needed portion.
CASE
WHEN @OutPatient>0 THEN 'Outpatient Therapy'
WHEN @OutPatient=0 and @MedCheck>0 THEN 'Outpatient Medical Services'
WHEN @OutPatient=0 and @MedCheck=0 and @PsychRehab >0 THEN 'Psychiatric Rehab Services'
END as Column1
Here's how it looks in it's working state:
SELECT A.cl_id,
A.cl_ln,
A.cl_fn,
CASE
WHEN ((SELECT COUNT(*) AS OutCount FROM eventstb E WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 110 AND 113 OR
e_service BETWEEN 180 AND 250 OR
e_service = 280) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))>0) THEN 'Outpatient Therapy'
WHEN ((SELECT COUNT(*) AS OutCount2 FROM eventstb E WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 110 AND 113 OR
e_service BETWEEN 180 AND 250 OR
e_service = 280) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))=0 and
(SELECT COUNT(*) AS OutCount3
FROM eventstb E
WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 400 AND 402) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))>0)
THEN 'Outpatient Medical Services'
WHEN ((SELECT COUNT(*) AS OutCount4 FROM eventstb E WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 110 AND 113 OR
e_service BETWEEN 180 AND 250 OR
e_service = 280) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))=0 and
(SELECT COUNT(*) AS OutCount5
FROM eventstb E
WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 400 AND 402) AND (e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))=0 and
(SELECT COUNT(*) AS OutCount6
FROM eventstb E
WHERE A.cl_id = E.e_case_no and
(e_service BETWEEN 600 AND 609 OR
e_service BETWEEN 640 AND 685) AND
(e_date BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND
CONVERT(DATETIME, '2007-07-31 00:00:00', 102)) AND
(e_att BETWEEN '1' AND '3'))>0)
THEN 'Psychiatric Rehab Services'
END as Column1
FROM CLIENTSTB A
WHERE (A.cl_type = 'AC') AND (A.cl_regis = '01') AND (cl_adm_dt < CONVERT(DATETIME, '2007-07-01 00:00:00', 102)) AND
EXISTS(SELECT L.cl_ly_id, L.cl_ly_el_id_1 FROM layercl L
WHERE A.cl_id = L.cl_ly_id and (L.cl_ly_el_la_dt IS NULL) AND (L.cl_ly_fs IN (301, 302)))
ORDER by cl_id ASC
This part of a larger query, but I cut it down to the needed portion.