CODEWITHSUNDEEP
sqlsql-servercase
SaintFrag
SaintFrag

Reputation: 333

SQL Server double case statement with variables inefficiencies

This may be merely a case of not knowing what to search for, so an answer with that direction is perfectly acceptable. I'm pushing data to my SQL server and have the following (simulated), which works, but seems very inefficient. I would ultimately prefer to eliminate all my variables.

--From PHP
declare @employeeid varchar(25) = '100971'
declare @reviewscore as tinyint = 52
declare @payclass as varchar(25) = 'salary management'
--/From PHP

declare @wagequartile as tinyint = (select quartile from activeemployees where EmployeeID= @employeeid)

declare @scorequartile as tinyint = (
select
    case
        when @reviewscore <= max1 then 1
        when @reviewscore <= max2 then 2
        when @reviewscore <= max3 then 3
        else 4
    end as ScoreQuartile
from ReviewScoreMatrix where PayClass=@payclass
)

select
    case
        when @scorequartile = 1 then ScoreQuartile1
        when @scorequartile = 2 then ScoreQuartile2
        when @scorequartile = 3 then ScoreQuartile3
        else 4
    end as PercentRaise
from RaisePercentMatrix
where WageQuartile = @wagequartile

I tried playing with using a CTE but couldn't quite figure out how that made it any better. Relevant section:

with cte1 as (
    select
        case
            when @reviewscore <= max1 then 1
            when @reviewscore <= max2 then 2
            when @reviewscore <= max3 then 3
            else 4
        end as [ScoreQuartile]
    from ReviewScoreMatrix where PayClass=@payclass
)
select
    case
        when (select top 1 ScoreQuartile from cte1) = 1 then ScoreQuartile1
        when (select top 1 ScoreQuartile from cte1) = 2 then ScoreQuartile2
        when (select top 1 ScoreQuartile from cte1) = 3 then ScoreQuartile3
        else 4
    end as PercentRaise
from RaisePercentMatrix
where WageQuartile = @wagequartile

I feel I'm on the right track with the CTE, but embedding the select from cte1 seems like the miss there. Any help is appreciated.

Upvotes: 0

Views: 78

Answers (1)

pwilcox
pwilcox

Reputation: 5753

If I'm transferring correctly, then your code can become this:

declare 
    @employeeid varchar(25) = '100971',
    @reviewscore as tinyint = 52,
    @payclass as varchar(25) = 'salary management';

select      PercentRaise = 
                case 
                when @reviewScore <= rsm.max1 then rpm.ScoreQuartile1
                when @reviewScore <= rsm.max2 then rpm.ScoreQuartile2
                when @reviewScore <= rsm.max3 then rpm.ScoreQuartile3
                else 4
                end
from        activeEmployees e
join        raisePercentMatrix rpm on e.quartile = rpm.wageQuartile
left join   reviewScoreMatrix rsm on rsm.payClass = @payclass
where       e.EmployeeID = @employeeid;

But if somewhere you have a table of review scores and a table of payclass assignments for each employee, then you can query all employees:

select      e.employeeId,
            PercentRaise = 
                case 
                when rs.reviewScore <= rsm.max1 then rpm.ScoreQuartile1
                when rs.reviewScore <= rsm.max2 then rpm.ScoreQuartile2
                when rs.reviewScore <= rsm.max3 then rpm.ScoreQuartile3
                else 4
                end
from        activeEmployees e
join        reviewScores rs on e.employeeId = rs.employeeId
join        payClassInfo pci on e.employeeId = pci.employeeId
join        raisePercentMatrix rpm on e.quartile = rpm.wageQuartile
left join   reviewScoreMatrix rsm on rsm.payClass = pci.payclass;

Upvotes: 1

Related Questions