Use same column value twice in a SQL case statement

Question

I have joined multiple views to get the following output for time taken by each Department to complete a task. The output looks like this:

ID     PROCESS  DEPT    SECONDS
------------------------------
S911    BATCH   SALES   24268
S911    BATCH   MKTG     2992
S911    BATCH   HR         77
S911    BATCH   FIN     14995
S911    BATCH   DATA      739
S911    BATCH   ITA      8988

I need the output to be this:

ID     PROCESS  DEPT_2     SECONDS
----------------------------------
S911    BATCH   NONEXEMPT   24345
S911    BATCH   EXEMPT      48990
S911    BATCH   OTHERS       2992

Can this possibly done with a case statement where exempt is sum of sales and HR non exempt is sum of finance ,sales data and ITA and if there are any other departments they need to be summed up as others.

Note: it works fine, when I do not use sales in both the case statements, when I do, its failing

case 
    when DEPT in ('SALES', 'HR') then 'NON_EXEMPT'
    when DEPT in ('FINANCE, 'SALES', 'DATA, 'ITA') then 'EXEMPT'
    else 'OTHERS'
end

Answer 1

WITH data(ID, PROCESS, DEPT, SECONDS) as (
    select * from values
        ('S911', 'BATCH', 'SALES', 24268),
        ('S911', 'BATCH', 'MKTG',  2992),
        ('S911', 'BATCH', 'HR',    77),
        ('S911', 'BATCH', 'FIN', 14995),
        ('S911', 'BATCH', 'DATA',   739),
        ('S911', 'BATCH', 'ITA',  8988)
)
select *,
    case 
        when DEPT in ('SALES', 'HR') then 'NON_EXEMPT'
        when DEPT in ('FINANCE, 'SALES', 'DATA, 'ITA') then 'EXEMPT'
        else 'OTHERS'
    end
from data 

gives:

Syntax error: unexpected 'SALES'. (line 13)

syntax error line 13 at position 53 unexpected ')'. (line 13)

Ok so lets remove that "bad" sales:

select *,
    case 
        when DEPT in ('SALES', 'HR') then 'NON_EXEMPT'
        when DEPT in ('FINANCE, 'DATA, 'ITA') then 'EXEMPT'
        else 'OTHERS'
    end
from data 

Syntax error: unexpected 'DATA'. (line 14)

syntax error line 14 at position 37 unexpected ','.

syntax error line 14 at position 44 unexpected ')'. (line 14)

Ah, the end quote is missing from Finance AND Data

select *,
    case 
        when DEPT in ('SALES', 'HR') then 'NON_EXEMPT'
        when DEPT in ('FINANCE', 'DATA', 'ITA') then 'EXEMPT'
        else 'OTHERS'
    end
from data 

so back to the original line with corrections:

select *,
    case 
        when DEPT in ('SALES', 'HR') then 'NON_EXEMPT'
        when DEPT in ('FINANCE', 'SALES', 'DATA', 'ITA') then 'EXEMPT'
        else 'OTHERS'
    end
from data 

gives:

so it working how I would expect. what you actually seem to want is to get three sets of results.

select
WITH data(ID, PROCESS, DEPT, SECONDS) as (
    select * from values
        ('S911', 'BATCH', 'SALES', 24268),
        ('S911', 'BATCH', 'MKTG',  2992),
        ('S911', 'BATCH', 'HR',    77),
        ('S911', 'BATCH', 'FIN', 14995),
        ('S911', 'BATCH', 'DATA',   739),
        ('S911', 'BATCH', 'ITA',  8988)
)
select
    id,
    process,
    'NONEXEMPT' as dept_2,
    sum(seconds) as seconds
from data 
where DEPT in ('SALES', 'HR')
group by 1,2

union all

select
    id,
    process,
    'EXEMPT' as dept_2,
    sum(seconds) as seconds
from data 
where DEPT in ('FIN', 'SALES', 'DATA', 'ITA')
group by 1,2


union all

select
    id,
    process,
    'OTHERS' as dept_2,
    sum(seconds) as seconds
from data 
where DEPT not in ('FIN', 'SALES', 'DATA', 'ITA', 'HR')
group by 1,2

Once we notice the input uses 'FIN' but the case uses 'FINACE' and make those the same we get:

It could also be flipped around like:

WITH data(ID, PROCESS, DEPT, SECONDS) as (
    select * from values
        ('S911', 'BATCH', 'SALES', 24268),
        ('S911', 'BATCH', 'MKTG',  2992),
        ('S911', 'BATCH', 'HR',    77),
        ('S911', 'BATCH', 'FIN', 14995),
        ('S911', 'BATCH', 'DATA',   739),
        ('S911', 'BATCH', 'ITA',  8988)
), classified_rows as (
    select *,
        'NONEXEMPT' as dept_2
    from data 
    where DEPT in ('SALES', 'HR')
    
    union all
    
    select *,
        'EXEMPT'
    from data 
    where DEPT in ('FIN', 'SALES', 'DATA', 'ITA')
    
    union all
    
    select *,
        'OTHERS'
    from data 
    where DEPT not in ('FIN', 'SALES', 'DATA', 'ITA', 'HR')
)
select 
    id, 
    process,
    dept_2, 
    sum(seconds) as seconds 
from classified_rows
group by 1,2,3

but I suspect on large data, the first form would have better performance as the GROUP BY is on small sets of data.