CODEWITHSUNDEEP
sqlsql-server
Q1980
Q1980

Reputation: 47

Why is my SQL case expression returning the wrong result?

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

Upvotes: 1

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 1

Related Questions