February 26, 2009 at 9:09 am
How would I try to get a value for Unknown if unknown can be a 1, 8 or 9. What is the syntax for writing that? I tried When (1 OR 8) and When 1 (OR 8) and even When 1,8 OR (9)
select case a.staff
when 6 then 'EM'
when 2 then 'IV'
when 3 then 'CR'
when 4 then 'EP'
when 5 then 'Other'
when 1 OR 8 then 'Unknown' --combine 1, 8 and 9
end as ' Level'
from org a
Group by a.staff,a.year
Having a.year = @year
Thanks,
Airborn
February 26, 2009 at 9:15 am
just use the other CASE syntax:
select case
when a.staff = 6 then 'EM'
when a.staff = 2 then 'IV'
when a.staff = 3 then 'CR'
when a.staff = 4 then 'EP'
when a.staff IN(5) then 'Other'
when a.staff IN(1,8,9) then 'Unknown' --combine 1, 8 and 9
end as ' Level'
from org a
Group by a.staff,a.year
Having a.year = @year
Lowell
February 26, 2009 at 9:30 am
I guess I should have worded it better - the statement you provided works..it pulls out the 1, 8 and 9 and calls them each "unknown"
1 ------------ value
8------------- value
9------------- value
How would it be combined to add all 1's, 8's and 9's to equal unknown?
Thanks again.
February 26, 2009 at 9:36 am
could you give sample data and an example of how you would like the output to look.
Your second wording wasn't any better :ermm:
February 26, 2009 at 9:38 am
airborn (2/26/2009)
I guess I should have worded it better - the statement you provided works..it pulls out the 1, 8 and 9 and calls them each "unknown"1 ------------ value
8------------- value
9------------- value
How would it be combined to add all 1's, 8's and 9's to equal unknown?
Thanks again.
If you want to group all 3 values together as unknown you need to group on the result of the case statement not the a.staff values because it will continue to group on the individual a.staff values (8,9,1) and not your unknown value.
select case
when a.staff = 6 then 'EM'
when a.staff = 2 then 'IV'
when a.staff = 3 then 'CR'
when a.staff = 4 then 'EP'
when a.staff IN(5) then 'Other'
when a.staff IN(1,8,9) then 'Unknown' --combine 1, 8 and 9
end as ' Level'
from org a
Group by select case
when a.staff = 6 then 'EM'
when a.staff = 2 then 'IV'
when a.staff = 3 then 'CR'
when a.staff = 4 then 'EP'
when a.staff IN(5) then 'Other'
when a.staff IN(1,8,9) then 'Unknown' --combine 1, 8 and 9
end,a.year
Having a.year = @year
February 26, 2009 at 10:39 am
Luke L -
That is exactly what I want to do. However, I tried the query and it gives me an error:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'select'.
And it points to the Group by Select Case
Any other suggestions???
Thanks!!
February 26, 2009 at 10:44 am
it's just a copy/paste error
change
Group by Select Case
to
Group by Case
Lowell
February 26, 2009 at 10:52 am
How about something like this?
with cteStaffYear as (
select
case
when a.staff = 6 then 'EM'
when a.staff = 2 then 'IV'
when a.staff = 3 then 'CR'
when a.staff = 4 then 'EP'
when a.staff IN(5) then 'Other'
when a.staff IN(1,8,9) then 'Unknown' --combine 1, 8 and 9
end as Level,
a.year as StaffYear
from
org a
)
select
Level,
StaffYear
from
cteStaffYear
Group by
Level,
StaffYear
Having
StaffYear = @year;
February 26, 2009 at 10:54 am
Thanks All!!! It worked.....
Also, Lowell I can't believe I didn't notice that Select stuck there. Oops.
Thanks again....
February 26, 2009 at 10:59 am
Sorry 'bout that, but I guess that what I get for not copying and pasting it into SSMS and just doing it on the forum...
I'm curious, which method is faster for your given scenario? The CTE that Lynn expertly put together or the modified case statement from Lowell?
-Luke.
February 26, 2009 at 11:06 am
The faster one for my purpose is yours Luke.
Thanks
February 26, 2009 at 11:10 am
Thanks for getting back to us, I was curious as I don't work with CTE's all that often as I still live in a sql 2000 world for the bulk of my production applications. Still trying to teach this old dog some new tricks...
Thanks again,
-Luke.
March 4, 2009 at 8:02 am
Thanks for all your help the last time....but when pulling the portion into my main query (as seen below) I get an error :
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near '='.
What am I not seeing?? I tried changing all the equals to IN, but that just produces another set of errors.
Please help.
Declare
@year int
SET @year = 2004
select
case a.month
when 1 then 'Jan ' + char(39)+ ' ' + substring(CONVERT(varchar(4),@year),3,2)
when 2 then 'Feb'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'Aug'
when 9 then 'Sept'
when 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec ' + char(39)+ ' ' + substring(CONVERT(varchar(4),@year),3,2)
end as Months
,county_DESC
,case a.no_care
when 7 then 'NC: D'
when 8 then 'NC: Cancelled'
when 9 then 'NC: False'
when 0 then 'NC: No Patient'
when 6 then 'NC: Refused'
when 10 then'NC: 2nd '
when 99 then'NC: Unknown'
end as 'NC'
,case a.transport
when 1 then 'Care: No Transport'
when 9 then 'Care: Unknown Transport'--8 or 9 - figure out a way to pull both
when 3 then 'Care: TransrAmb'
when 4 then 'Care: Trans Hel'
when 2 then 'Care: Trans Unit'
end as 'Transport By'
,case a.staff
when a.staff =6 then 'EM'
when a.staff =2 then 'IV'
when a.staff =3 then 'CR'
when a.staff =4 then 'EP'
when a.staff =5 then 'Other'
when a.staff (1,7,8,9) then 'Unknown' --combine 1, 8 and 9
end as 'Level'
,count(a.rcp)as Total
from dbo.agency a
inner join County_DEF c
on c.code = a.county
Group by a.month,county_DESC,a.no_care,a.year,a.transport,
case
when a.staff =6 then 'EM'
when a.staff =2 then 'IV'
when a.staff =3 then 'CR'
when a.staff =4 then 'EP'
when a.staff =5 then 'Other'
when a.staff IN(1,7,8,9) then 'Unknown'
end,
Having a.year = @year
Order by a.month
March 4, 2009 at 8:21 am
March 4, 2009 at 8:22 am
all minor syntax stuff; i found three things:
the higher case statement for a.staff said case a.staff andf then had WHEN a.staff...; should be one syntax or the other...
second was missing IN staement for the last item int eh same case, last was a comma after the GROUP BY and before HAVING.
[font="Courier New"]
DECLARE
@year INT
SET @year = 2004
SELECT
CASE a.MONTH
WHEN 1 THEN 'Jan ' + CHAR(39)+ ' ' + SUBSTRING(CONVERT(VARCHAR(4),@year),3,2)
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sept'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec ' + CHAR(39)+ ' ' + SUBSTRING(CONVERT(VARCHAR(4),@year),3,2)
END AS Months
,county_DESC
,CASE a.no_care
WHEN 7 THEN 'NC: D'
WHEN 8 THEN 'NC: Cancelled'
WHEN 9 THEN 'NC: False'
WHEN 0 THEN 'NC: No Patient'
WHEN 6 THEN 'NC: Refused'
WHEN 10 THEN'NC: 2nd '
WHEN 99 THEN'NC: Unknown'
END AS 'NC'
,CASE a.transport
WHEN 1 THEN 'Care: No Transport'
WHEN 9 THEN 'Care: Unknown Transport'--8 or 9 - figure out a way to pull both
WHEN 3 THEN 'Care: TransrAmb'
WHEN 4 THEN 'Care: Trans Hel'
WHEN 2 THEN 'Care: Trans Unit'
END AS 'Transport By'
,CASE
WHEN a.staff =6 THEN 'EM'
WHEN a.staff =2 THEN 'IV'
WHEN a.staff =3 THEN 'CR'
WHEN a.staff =4 THEN 'EP'
WHEN a.staff =5 THEN 'Other'
WHEN a.staff IN (1,7,8,9) THEN 'Unknown' --combine 1, 8 and 9
END AS 'Level'
,COUNT(a.rcp)AS Total
FROM dbo.agency a
INNER JOIN County_DEF c
ON c.code = a.county
GROUP BY a.MONTH,county_DESC,a.no_care,a.YEAR,a.transport,
CASE
WHEN a.staff =6 THEN 'EM'
WHEN a.staff =2 THEN 'IV'
WHEN a.staff =3 THEN 'CR'
WHEN a.staff =4 THEN 'EP'
WHEN a.staff =5 THEN 'Other'
WHEN a.staff IN(1,7,8,9) THEN 'Unknown'
END
HAVING a.YEAR = @year
ORDER BY a.MONTH
[/font]
Lowell
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply