Trying to combine two different case statements

Hey everyone,

 

I am currently trying to combine two case statements. 

 

I have written this statement:

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'

When DATEDIFF(`actual_ship_date`,`release_date`) <= 5 THEN 'D'

When DATEDIFF(`actual_ship_date`,`release_date`) > 5 Then 'F'

End

 

I am using this statement to give the warehouses a grade depending on how quickly they ship out a given order. But the problem with this is that it does not remove the weekend (our warehouse is closed on saturday and Sunday). I would like it to remove the weekend and still give each warehouse the appropriate grade with the weekends not accounted for.

 

I have this statement now to remove the weekends, I am trying to comine these two statements so that it will still remove the weekend and give each warehouse a grade:

 

 

AVG(CASE WHEN `release_date` = `actual_ship_date` THEN 0 ELSE DATEDIFF(

CASE -- move end date to friday if on weekend
WHEN dayofweek(`actual_ship_date`) = 1 THEN SUBDATE(`actual_ship_date`, INTERVAL 1 DAY)
ELSE `actual_ship_date`
END
,

CASE -- move start date to monday if on weekend
WHEN dayofweek(`release_date`) = 7 THEN ADDDATE(`release_date`, INTERVAL 2 DAY)
WHEN dayofweek(`release_date`) = 1 THEN ADDDATE(`release_date`, INTERVAL 1 DAY)
ELSE `release_date`
END)

-

(-- finds number of weeks and then times by 2 to get number of weekend days
FLOOR((DATEDIFF(
CASE -- move end date to friday if on weekend
WHEN dayofweek(`actual_ship_date`) = 7 THEN SUBDATE(`actual_ship_date`, INTERVAL 1 DAY)
WHEN dayofweek(`actual_ship_date`) = 1 THEN SUBDATE(`actual_ship_date`, INTERVAL 2 DAY)
ELSE `actual_ship_date`
END,

CASE -- move start date to monday if on weekend
WHEN dayofweek(`release_date`) = 7 THEN ADDDATE(`release_date`, INTERVAL 1 DAY)
WHEN dayofweek(`release_date`) = 2 THEN SUBDATE(`release_date`, INTERVAL 1 DAY)
WHEN dayofweek(`release_date`) = 3 THEN SUBDATE(`release_date`, INTERVAL 2 DAY)
WHEN dayofweek(`release_date`) = 4 THEN SUBDATE(`release_date`, INTERVAL 3 DAY)
WHEN dayofweek(`release_date`) = 5 THEN SUBDATE(`release_date`, INTERVAL 4 DAY)
WHEN dayofweek(`release_date`) = 6 THEN SUBDATE(`release_date`, INTERVAL 5 DAY)
ELSE `release_date`
END

) / 7)) * 2)

END)

 

 

I have attatched a image of the card i am making. It is currently set up only using the first case statement that I listed and is doing exactly what I want it to do besides accomodate for the weekend. That is why I would like to combine these two statements to make it work. Does anyone have any advice on how to go about this?

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    Another option is to go into a SQL dataflow and calculate this grade you're wanting ahead of time. You could something like this:

     

    SELECT *,
    CASE

    WHEN (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) < 2 THEN 'A'

    WHEN (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) <= 3 THEN 'B'

    WHEN (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) <= 4 THEN 'C'

    When (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) <= 5 THEN 'D'

    When (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) > 5 Then 'F'

    End AS 'Grade'
    FROM dataset

    That would add the grade letters you're looking for and you could then use this new dataset on your card.

     

    Sincerely,

    Valiant

Answers

  • Hi, user 03552. That's a lot of code ?

     

    I find it is sometimes easier to combine case statements (multiple conditions for each when/then) rather than nesting them. So, for example, instead of this:

    case
    when x = y then
    case
    when a = b then 'This'
    else 'That'
    end
    else 'Something else entirely'
    end as `Column Name`

    try this:

    case
    when x = y and a = b then 'This'
    when x = y then 'That'
    else 'Something else entirely'
    end as `Column Name`

    It is often a lot easier to read and de-bug. I'd suggest refactoring your nested case statements into combined case statements and see if that help at all.

     

    Using this method, your first case statement would look something like this:

    avg(
    case
    when `release_date` = `actual_ship_date` then 0
    when `release_date` <> `actual_ship_date` and dayofweek(`actual_ship_date`) = 1 and dayofweek(`release_date`) = 7 then datediff(subdate(`actual_ship_date`, interval 1 day),adddate(`release_date`, interval 2 day))
    when `release_date` <> `actual_ship_date` and dayofweek(`actual_ship_date`) = 1 and dayofweek(`release_date`) = 1 then datediff(subdate(`actual_ship_date`, interval 1 day),adddate(`release_date`, interval 1 day))
    when `release_date` <> `actual_ship_date` and dayofweek(`actual_ship_date`) = 1 and dayofweek(`release_date`) not in (7,1) then datediff(subdate(`actual_ship_date`, interval 1 day),`release_date`)
    when `release_date` <> `actual_ship_date` and dayofweek(`actual_ship_date`) <> 1 and dayofweek(`release_date`) = 7 then datediff(`actual_ship_date`,adddate(`release_date`, interval 2 day))
    when `release_date` <> `actual_ship_date` and dayofweek(`actual_ship_date`) <> 1 and dayofweek(`release_date`) = 1 then datediff(`actual_ship_date`,adddate(`release_date`, interval 1 day))
    when `release_date` <> `actual_ship_date` and dayofweek(`actual_ship_date`) <> 1 and dayofweek(`release_date`) not in (7,1) then datediff(`actual_ship_date`,`release_date`)
    end
    )

    Hope that helps. 

  • Valiant
    Valiant Coach
    Answer ✓

    Another option is to go into a SQL dataflow and calculate this grade you're wanting ahead of time. You could something like this:

     

    SELECT *,
    CASE

    WHEN (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) < 2 THEN 'A'

    WHEN (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) <= 3 THEN 'B'

    WHEN (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) <= 4 THEN 'C'

    When (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) <= 5 THEN 'D'

    When (5 * FLOOR(DATEDIFF(`actual_ship_date`, `release_date`)/ 7) )
    + SUBSTRING('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(`release_date`) + WEEKDAY(`actual_ship_date`) + 1, 1) > 5 Then 'F'

    End AS 'Grade'
    FROM dataset

    That would add the grade letters you're looking for and you could then use this new dataset on your card.

     

    Sincerely,

    Valiant

  • Your method is definitely much cleaner! Using this method how would I incorporate the scoring for the grades, because I am still running into the same issue.

  • I like this idea, I will have to try to do this. Once I have tried I will let you know how it goes.