CASE Statement

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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:

  • 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

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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!!

  • it's just a copy/paste error

    change

    Group by Select Case

    to

    Group by Case

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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;

  • Thanks All!!! It worked.....

    Also, Lowell I can't believe I didn't notice that Select stuck there. Oops.

    Thanks again....

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • The faster one for my purpose is yours Luke.

    Thanks

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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

  • You've got a comma after your Case Statement in your Group BY before your HAving Clause.

    Get rid of that and you should be all set.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 14 (of 14 total)

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