Beast Mode Turning numbers into a Letter Grade

user03552

Hey everyone, 

 

I am currently working on a project where I have built a data set to see the turn around times on our containers coming in to our warehouses. I am trying to make it so that it will assign each warehouse a letter grade depending on how many days it takes them to empty out a shipping container. I have been trying to write a statement so that it will work but I keep getting a syntax error. Im basically trying to change a range of numbers into letter grades just like they would in high school.

 

Here are the different statements I have written. None of which have worked thus far. I don't paticularly care which one I use I am just trying to get it to work. 

 

Thanks for your help!

 

Attempt 1:

(CASE

when `actual_ship_date`-`release_date` >=2 then "A"

when `actual_ship_date`-`release_date`between 2.001 and 3.00 then "B"

when `actual_ship_date`-`release_date`between 3.001 and 4.00 then "C"

When `actual_ship_date`-`release_date`between 4.001 and 5.00 then "D"

when `actual_ship_date`-`release_date`> 5.00 then "F"

else "null")

 end

 

Attempt 2:

if (`actual_ship_date`-`release_date` >=2)

grade = "A";

else if (`actual_ship_date`-`release_date` >= 2.01 && `actual_ship_date`-`release_date` <= 3)

Grade = "B";

else if (`actual_ship_date`-`release_date` >= 3.01 && `actual_ship_date`-`release_date` <= 4)

Grade = "C";

else if (`actual_ship_date`-`release_date` >= 4.01 && `actual_ship_date`-`release_date` <= 5)

Grade = "D";

else if (`actual_ship_date`-`release_date` >= 5.01)

Grade = "F";

End

 

Attempt 3:

(CASE

 when `actual_ship_date`-`release_date`< 2 then "A"

 when `actual_ship_date`-`release_date`< 3 then "B"

 When `actual_ship_date`-`release_date`< 4 then "C"

 When `actual_ship_date`-`release_date`< 5 then "D"

 When `actual_ship_date`-`release_date`>= 5 then "F"

 Else "Null")

AS

It's probably either in your date subtraction or in your assignment of the grade. 

I've found it works best to use the DATEDIFF() function instead of simply subracting like you might in certain database queries. BETWEEN doesn't always work well in beast modes, either.

String outputs should also be surrounded by single quotes.

CASE

WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 2 THEN 'A'

WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 3 THEN 'B'

WHEN DATEDIFF(`actual_ship_date`,`release_date`) < 4 THEN 'C'

...

END

user03552

Good to know!

 

And thank you for the help