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

Options
Member
edited April 2023

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?

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

• Coach
Options

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

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

• Coach
Options

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

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
• Member
Options

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

• Coach
Options

no.  You just missed a comma after `total_revenue_usd`

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
• Member
Options

Doh!

Thanks Scott.

• Coach
Options

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

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
• Coach
Options

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

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
• Member
Options

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?

• Contributor
Options

@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?