Trying to combine two different case statements

user03552 · September 2018

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?

BlueRooster · September 2018

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

Unknown · September 2018

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.

BlueRooster · September 2018

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

user03552 · September 2018

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.

user03552 · September 2018

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

Trying to combine two different case statements

Best Answer

Answers

Categories