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

Find more posts tagged with

Accepted answers

ST_Superman

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

All comments

ST_Superman

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

BV_Brandon

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

ST_Superman

no. You just missed a comma after `total_revenue_usd`

BV_Brandon

Doh!

Thanks Scott.

ST_Superman

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

ST_Superman

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

cpbwg

When we do DATEFIFF, it yields a "blue" dimension beast mode calculation and not a "green" Measure calculation. This is problematic because we want to do alerts based on the measure, but it doesn't offer the ability to alert (or do a summary number) because it's not a measure. Seems like a bug? Anyone know a workaround to get the days between two dates as a measure?

Screen Shot 2022-10-26 at 8.18.22 PM.png

mhouston

@cpbwg i think you can force it to numeric by multiplying by 1 as a workaround. I tested this in my own instance and the date_diff() function gave me a measure type field without doing this workaround. Have you confirmed you don't have any unexpected values in your dataset? Are your date fields actually date types?