Between (2 percentages) Function in Case Statement with Calculation

[chrisjab]

Good Afternoon,

I am trying to create a case statement where the defined criteria are in percentages and the conditions are in Dollars; given the differences between the criteria and the defined conditions I am running into rounding issues resulting in transactions being left out.

For example, I have 3 columns identified as: 

1. Sale_Beverage
2. Sale_Food
3. Total_ Sales 

With this information, I am trying to group the percentage of Beverage and Food sales into the following defined categories:

1. If Total_Sales = $0 then "No Activity"
2. If (Sale_Food/ Total_Sales) >= 80% then "FoodH"
3. If (Sale_Food/ Total_Sales) between 55% and 79% then "FoodM"
4. If (Sale_Food/ Total_Sales) between 45% and 54% the "FoodL"
5. .....

Given the criteria this is what I came up with:

CASE
WHEN Total_Sales = 0
THEN "No Activity"
WHEN (Sale_Food/Total_Sales)>= 0.80 -- for 80%
THEN "FoodH"
WHEN (Sale_Food/Total_Sales) BETWEEN 0.55 and 0.79 -- for between 55%  and 79%

THEN "FoodM"

...

END

Any help would be gladly appreciated.

Thanks,

Chris

[PGStats · Accepted solution]

Use the fact that case clauses are tested in order until one matches. Make sure you cover all possibilities

CASE
WHEN Total_Sales = 0
	THEN "No Activity"
WHEN (Sale_Food/Total_Sales) < 0.45
	THEN "Food?"
WHEN (Sale_Food/Total_Sales) < 0.55
	THEN "FoodL"
WHEN (Sale_Food/Total_Sales) < 0.80
	THEN "FoodM"
	ELSE "FoodH"
END as Category

[chrisjab]

Thank you PG !