Help with a DATEDIFF calculation to create multiple ranges to sort on

Hi, i'm trying to create a card to segment the "Time to Spend" for new accounts. Check the calc below, I hope it is self explanatory. It almost works! End result is that it only creates 2 different segments. I get either 'Same Day' or 'Spent month or more later'. No range inbetween gets defined. Do I need to nest multiple case statements or do I just have the syntax off somewhere? Can I use AND that way?

 

Thanks in advance!

 

CASE
WHEN DATEDIFF(`created_at`,`first_invoiced`) = 0 THEN 'Same Day'
WHEN DATEDIFF(`created_at`,`first_invoiced`) = 1 THEN 'Next Day'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 2 AND DATEDIFF(`created_at`,`first_invoiced`) <= 3 THEN '2-3 Days'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 4 AND DATEDIFF(`created_at`,`first_invoiced`) <= 7 THEN 'One Week'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 8 AND DATEDIFF(`created_at`,`first_invoiced`) <= 14 THEN '2 Weeks'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 14 AND DATEDIFF(`created_at`,`first_invoiced`) <= 31 THEN '2-4 Weeks'
ELSE 'Spent month or more later'
END

 

 

Best Answer

  • ST_-Superman-_
    Accepted Answer

    try flipping the values your are looking for to negatives

     

    DATEDIFF takes the first date minus the second date.  If I created the account yesterday and bought today, 4/25/2018 - 4/26/2018 would give you -1; not 1.

     

    Either switch the sign, or change the order of the dates, or take an ABS() of the calculation

Answers

  • I'm trying other options while waiting for someone to reply. I stepped back to try and address it in the original SQL query but now i'm running into syntax issues. I'm still getting comfortable with SQL and i'm struggling to see where this query goes wrong. I'm guessing that creating the new column with the calculation already made will simplify the beast mode calc. Though I imagine its just two ways of getting to the same place. Is that right?

     

    SELECT
    `created_at`,
    `first_invoiced`,
    `last_invoiced`,
    `payment_terms`,
    `pcode`,
    `status`,
    `total_invoices`,
    `total_revenue_usd`
    DATEDIFF(`created_at`,`first_invoiced`) AS `Time to Spend`
    FROM accounts

  • no.  You just missed a comma after `total_revenue_usd`

  • Doh!

     

    Thanks Scott. 

  • No worries @BV_Brandon.  I was in your shoes a year ago, new to SQL.  Take full advantage of the Dojo and don't be afraid to ask questions.

     

    The Domo support number was also a great resource while I was getting up to speed.  

    801.805.9505

  • One more bit of advice on this Case Statement.  The case statement runs through the when statements until it finds one that is true and then it stops.  You can simplify this statement a bit if you understand that:

     

    CASE
    WHEN DATEDIFF(`created_at`,`first_invoiced`) = 0 THEN 'Same Day'
    WHEN DATEDIFF(`created_at`,`first_invoiced`) = -1 THEN 'Next Day'
    WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -3 THEN '2-3 Days'
    WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -7 THEN 'One Week'
    WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -13 THEN '2 Weeks'
    WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -30 THEN '2-4 Weeks'
    ELSE 'Spent month or more later'
    END