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?