title: SQL Server double case statement with variables inefficiencies
body: <p>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.</p>
  <pre><code>--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 &lt;= max1 then 1
          when @reviewscore &lt;= max2 then 2
          when @reviewscore &lt;= 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
  </code></pre>
  <p>I tried playing with using a CTE but couldn't quite figure out how that made it any better.  Relevant section:</p>
  <pre><code>with cte1 as (
      select
          case
              when @reviewscore &lt;= max1 then 1
              when @reviewscore &lt;= max2 then 2
              when @reviewscore &lt;= 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
  </code></pre>
  <p>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.</p>