Date Range Buckets

Hello,

 

I am trying to create a way to look at upcoming estimated shipping dates but I keep getting an "error during processing" .

 

I have made buckets for WIP based off days in shop field and basic case formulas in the past with no issues (See attached).

 

I think I am having issues with my approach due to this being dates instead of a raw value.

 

In theory: 

case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’ else

case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’ else

case when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’ else

case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days)  then ‘next 21 days’ else

'Over 21 days' end end end end

 

 

Tagged:

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    your syntax for a case statement is wrong.

     

    case

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’ 

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’ 

    when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days)  then ‘next 21 days’

    else 'Over 21 days'

    end

     

     

     

Answers

  • I think you are missing a comma in the DATE_ADD statement. Try changing

    DATE_ADD(CURRENT_DATE()interval 3 days)

    to:

    DATE_ADD(CURRENT_DATE(), interval 3 days)

     

    Also I'm not sure why you have so many case/end statements?  I use the following block of code for date buckets, and it works fine else-chaining without the Else/Case between, thought you might want to see to possibly shorten your code.

     

    Case
    when DATEDIFF(`Event Date`,`Boxed Date`) < 0 then 'Prior to Box Date'
    when DATEDIFF(`Event Date`,`Boxed Date`) < 91 then 'Under 3 Months'
    when DATEDIFF(`Event Date`,`Boxed Date`) < 181 then '3-6 Months'
    when DATEDIFF(`Event Date`,`Boxed Date`) < 271 then '6-9 Months'
    when DATEDIFF(`Event Date`,`Boxed Date`) < 366 then '9-12 Months'
    when DATEDIFF(`Event Date`,`Boxed Date`) > 365 then 'Over 12 Months'
    when `Event Date`is null then 'Production'
    else 'Unknown Timeframe Error'
    
    end

     

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!