Why is my SQL case expression returning the wrong result?

Question

My data looks something like this. The meaning of this is ID 123 had a first pay period of 1 (Jan) and last of 12 (Dec). ID 456 started in 3 (Mar) and ended in 12. The ... are all the other months in between that I didn't feel like typing, but lets pretend that all those records are there.

ID        Pay Period
--------------------
123          1
456          3
...
123          12
456          12

I am trying to write code that will do the following.

First find the min and max pay period. Once I find these, I want my case expression to say that if the first min pay period is greater than 1, and the last period is 12, that means they started in the middle of the year an worked till Dec and I want to consider them as a "New Employee". If they started in Jan and worked through Dec they are what I am calling a "12 Month Employee".

But the code below is calling everyone a New Employee. What did I do wrong?

select 
    empee_ID,
    case 
        when min_pay_rng > 1 and max_pay_rng = 12 or max_pay_rng = 26 
           then 'New_Emp'
           else '12 Mnth Emp'
    end
from 
    (select 
         empee_ID, min(payr_rng) as min_pay_rng, max(payr_rng) as max_pay_rng
     from 
         Data
     where 
         1 = 1
         and empee_ID in (123, 456)
     group by 
         empee_ID) pay_range

My results look like this

empee_ID         (No column name)
---------------------------------
123               New_Emp
456               New_EmP

And I want them to look like this

empee_ID         (No column name)
---------------------------------
123               12 Mnth Emp
456               New_EmP

Gordon Linoff · Accepted Answer · 2020-01-30 22:33:41Z

1

I'm not sure where the "26" is coming from, but I think the logic you want is:

select empee_ID,
       (case when min(payr_rng) > 1 and max(payr_rng) in (12, 26)
             then 'New Employee'
             when min(pay_rng) = 1 and max(payr_rng) in (12, 26)
             then '12-Month'
             else 'Former'
        end)
from Data
where 1=1
and empee_ID in (123,456)
group by empee_ID;

This code actually has a bug. An employee paid biweekly who starts 12 pay periods from the end of the year will not be noted as "new". Your data does not have enough information to distinguish this case, unless you have dates or other information.

answered Jan 30, 2020 at 22:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shawnt00 Over a year ago

I'm guessing that some are monthly and some are biweekly, which is another potential bug.

Q1980 Over a year ago

Thank you, that logic you provided worked. I am not sure why I did a sub-query, but removing it was the key! Also @shawnt00, yes, some are monthly and some are biweekly, hence 12 and 26.

Collectives™ on Stack Overflow

Why is my SQL case expression returning the wrong result?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related