Beast Mode Turning numbers into a Letter Grade

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:


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")



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";



Attempt 3:


 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")

Best Answer

  • AS
    AS 🔵
    Answer ✓

    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.


    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'



    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"