Trying to combine two different case statements

user03552

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?

Valiant

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

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

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

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

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