SQL - case statement

Understanding Case Expression in SQL Server with Example

1. -- Simple CASE expression:
2. SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
3. FROM dbo.Customer
4. GROUP BY StateCode,Gender,FirstName
5. HAVING (MAX(CASE Gender WHEN 'M'
6. THEN PayRate
7. ELSE NULL END) > 180.00
8. OR MAX(CASE Gender WHEN 'F'
9. THEN PayRate
10. ELSE NULL END) > 170.00)
11.  
12. -- Searched CASE expression:
13. SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
14. FROM dbo.Customer
15. GROUP BY StateCode,Gender,FirstName
16. HAVING (MAX(CASE WHEN Gender = 'M'
17. THEN PayRate
18. ELSE NULL END) > 180.00
19. OR MAX(CASE WHEN Gender = 'F'
20. THEN PayRate
21. ELSE NULL END) > 170.00)