SQL Case Statement Compare

If I have
      AgtNo      Sales      Tier1      Tier2
      123                 10                    5      8

So agent 123 sold 10 units and if an agent's sales exceeds Tier1 they get 200 bonus if they exceed Tier2 they get 500. If they hit Tier 2 they are excluded from Tier 1. In other words this agent would win 500 and NOT 700 (200 + 500)
I want to write a Case Statement
Case When Sales >= Tier1 Then 200
          When Sales >= Tier2 The 500  else 0 end as Payout.

My problem is that Case statement produces a 200 result, thus ignores the Tier2 portion.
What am I doing wrong?
Thanks
SeTechAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
The tests occur in the order they're programmed.

Case When Sales >= Tier2 Then 500
          When Sales >= Tier1 Then 200  else 0 end as Payout.

That should work just fine.  :)

Good Luck,
Kent
0
 
SeTechAuthor Commented:
Works Perfect - thanks
0
 
Ryan ChongCommented:
the root of the issue was that you forgot to put the limit condition:

Case When Sales >= Tier1 and Sales < Tier2 Then 200
     When Sales >= Tier2 Then 500  else 0 end as Payout

but we usually use the approach as what Kent had proposed above. it's much more easier to code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.