How to create date series with beast mode

wgechter

I'm trying to create a series of data: Same Day, 0-14 Days, 15-30 Days, 31-60 Days, etc

 

I have 2 fields Created_Date and Closed_Date.  If created_date = Closed_Date then "Same Day", if Closed_date - created_date < 14 days then 0-14 Days and so on.  

 

My beast mode looks like this below.  I was trying some stuff but anything over 30 days isn't working.  I've tried date_sub, datediff, subdate, day but nothing seems to be working.  

case when datediff(`Closed Date`, `Created Date`) = 0 then 'Same Day'
when DAY(`Closed Date`) - DAY(`Created Date`) < 14 then '0-14 Days'
when DAY(`Closed Date`) - DAY(`Created Date`) < 30 then '15-30 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 60 then '31-60 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 90 then '61-90 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 120 then '91-120 Days'
when DATEdiff(`Closed Date`, `Created Date`) > 120 then '> 120 Days' end

 

Thoughts?  Wendi

wgechter

Nevermind I just corrected it by doing datediff to all columns

 

case when datediff(`Closed Date`, `Created Date`) = 0 then 'Same Day'
when DATEdiff(`Closed Date`, `Created Date`) < 14 then '0-14 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 30 then '15-30 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 60 then '31-60 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 90 then '61-90 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 120 then '91-120 Days'
when DATEdiff(`Closed Date`, `Created Date`) > 120 then '> 120 Days' end

jhl

Just a quick note: 

Your earlier iteration wasnt working (even for less than 30 days, but that would not have been obvious immediately) because of some quirks of SQL-based languages. Most of the date function take only one thing into account - in DAY() it is the day number of the month. This means if something is, say, created on the 31st of January and then closed on the 30th of March, that would show as '0-14 Days' (31-30=1).

Another thing you might want to keep in mind are year changes - datediff works for the number of days, but if you are interested in the number of months (and you want to avoid doing a complex beast mode to make sure something like 92 days becomes either 2 or 3 full months, depending on the date and number of months). We had to rework quite a bit of code when we realized that we couldnt use MONTH() AND YEAR() to account for year changes easily - PERIOD_DIFF() works well here (usually requires a DATE_FORMAT() though).

wgechter

Ahh thank you!. Nice to know the information.  Date_diff is working very well with my data when validating.