Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Case Expression

Status
Not open for further replies.

Memento

MIS
Jun 19, 2005
46
US
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.
 
What is the question??

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

[monkey][snake] <.
 
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.
 
The following case statement is equivalent to yours if @OutPatient and @MedCheck can't be negative or NULL.

Code:
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]
 
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


 
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]
 
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
 
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'."



 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top