# Nested Case Statement

I'm trying to count travel records that are completed and then put them into tiers. ·

0-34 Tier 1

35-70 Tier 2

71-110 Tier 3

111+ Tier 4

I keep getting an error using the beast mode below

CASE WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 35 THEN 'TIER 2'

WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 71 OR > 110 THEN 'TIER 3'

WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c` END) > 111 THEN 'TIER 4'

ELSE 'TIER 1'

END)

• Coach You need to reverse the order of your SUM checks because if the sum is more than 111 it's already more than 35 so it will return Tier 2 first and quit as it executes the first case that matches. Check for Tier 4 then 3 then 2 then 1.

Also with your tier 3 check you only need to check for 71 because by logic all prior when statements are false.

```CASE
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c` END) > 111 THEN 'TIER 4'
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 71 THEN 'TIER 3'
WHEN SUM(CASE WHEN `Status__c` = 'Booked' THEN 'Completed' ELSE `Status__c`END) > 35 THEN 'TIER 2'
ELSE 'TIER 1'

END)
```
**Did this solve your problem? Accept it as a solution!**

• @GrantSmith Thank you for your help!!

• Looks like I have to create this change in the Dataflow because

SELECT

a.*

,COUNT(DISTINCT(case WHEN a."Status__c"='Booked' and (a."Status__c" = 'Booked' > 111 THEN 'TIER 4'))

FROM

"sfdc_travel_raw" a

I am getting an error