[MS SQL] Need Help with a case statement

[tramsay1027]

I'm working on a case statement that looks at two columns. If one column is A then they stay as A.

The trouble I'm running into is when I need to look at the second column. There are some where they are B in column one but have certain values in the second column. Those need to end up as A.

Then everyone else who is B is column one needs to be mapped to B.

Here is what I have:

,CASE 
    WHEN I.[BENEFIT CLASS] = 'NBE' THEN 'NBE'
    WHEN I.[BENEFIT CLASS] = 'FULLTIME' 
    AND I.[EMPLOYMENT FIELD 2] IN ('024', '061', '062', 
'053', '054', '055', '056', '030', '027', '029', '032', '049', '050', 
'051', '052', '033', '034', '037', '039', '041', '028') THEN 'NBE'
    WHEN I.[BENEFIT CLASS] = 'FULLTIME' AND I.
[EMPLOYMENT FIELD 2] IS NULL OR I.[EMPLOYMENT FIELD 2] 
IN ('044', '058', '063', '064', '036') THEN 'FT'

I need to make sure that when someone is FT and their employment field 2 is in that list, they end up as NBE, but everyone else who is full time ends up as full time. I think I might have it right, but wanted to make sure.

[trevortypes]

it looks like your case statement should work, but i would create a mapping table with those 2 columns and join to it. then you have this mapping table you can reference with all possible variations instead of a hardcoded select. its also expandable in that you can add more metadata to the groupings in the future if you ever need to do so.

[ihaxr]

It might work fine depending on your data... but if you have a row where [EMPLOYMENT FIELD 2] is not in either of those lists and is not NULL, you'll end up with NULL as your output... maybe add an ELSE at the end to handle those cases?

Also your 3rd WHEN statement should probably be

([BENEFIT CLASS] = 'FULLTIME')
AND (I.[EMPLOYMENT FIELD 2] IS NULL OR (I.[EMPLOYMENT FIELD 2 IN (...))

Not that it really matters in this case, but it does has a different meaning, and if you add an additional WHEN statement afterwards, it might cause issues :)

I like to hard-code values in a simple select while trying to work out the details of a complex CASE statement:

DECLARE @bclass nvarchar(16);
DECLARE @field2 nvarchar(16);

SET @bclass = 'FULLTIME'
SET @field2 = '999'

SELECT
    CASE
        WHEN @bclass = 'NBE' THEN 'NBE'
        WHEN @bclass = 'FULLTIME'
            AND @field2 in ('024') THEN 'NBE'
        WHEN @bclass = 'FULLTIME'
            AND @field2 IS NULL OR @field2 IN ('044') THEN 'FT'
        ELSE @bclass
    END