Case Expression

[Memento]

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.

 

[monksnake]

What is the question??

And if it's how to do that, what errors are you getting right now?

<.

 

[Memento]

Monksnake,

I would like my code to be like this:

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

The long drawn out code is how it currently works. The way it works now is so ineffcient. I have to use the same query several times over. I'd like a suggestion on how to reduce the lines of code.

 

[ESquared]

The following case statement is equivalent to yours if @OutPatient and @MedCheck can't be negative or NULL.

CASE
   WHEN @OutPatient > 0 THEN 'Outpatient Therapy'
   WHEN @MedCheck > 0 THEN 'Outpatient Medical Services'
   WHEN @PsychRehab > 0 THEN 'Psychiatric Rehab Services'
END

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[Memento]

I think I'm doing a poor job of explaining what I need help with. Sorry for that.

How do I write the above case statement with variables? For example...This doesn't work because the A.cl_ID is before the Select. How do I get this to work?

DECLARE @OutPatient varchar(1000)

SET @Outpatient = ((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')))
SELECT A.cl_id,
A.cl_ln,
A.cl_fn,
CASE
WHEN @OutPatient>0 THEN 'Outpatient Thearpy'
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

 

[ESquared]

I have no idea. Provide more information please.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[Otto]

Try to create a sub query from your existing query and put the case above all. I mean something like this:

select *,
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
from (
select
(SELECT COUNT(*) AS OutCount FROM eventstb E WHERE ...) as OutPatient,
... as MedCheck, ... from Your_existing_query
where ...
) Q2

 

[Memento]

Otto, I think your right, but I couldn't apply the structure you supplied with what I had.

I think my problem is I would like to use the "AS Outpatient" furth down my SQL staement.

For example:
SELECT A.cl_id,
A.cl_ln,
A.cl_fn,
((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'))) as Outpatient,

Case
WHEN OutPatient>0 THEN 'It works'
End as Test
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

When I run it, I get,
"Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Outpatient'."

 

[Memento]

A little more info. I have to have the service provided and the frequency in two separate fields. The OutPatient query returns the Frequency, but then I have to also deliver the service name.