Tenure calculation using differences between 2 dates (magic etl)

Hi,

I am creating a new variable using the add formula icon where I am creating time period bins that reflects the tenure of the consultant in reference to today. This is the formula I created:

case
when CURRENT_DATE() -`CD (Start)` < 90 then '3 Months'
when CURRENT_DATE() -`CD (Start)` >= 90 and CURRENT_DATE() -`CD (Start)` < 180 then '3-6 Months'
when CURRENT_DATE() -`CD (Start)` >= 180 and CURRENT_DATE() -`CD (Start)` < 270 then '6-9 Months'
when CURRENT_DATE() -`CD (Start)` >= 270 and CURRENT_DATE() -`CD (Start)` < 360 then '9-12 Months'
when CURRENT_DATE() -`CD (Start)` >= 360 and CURRENT_DATE() -`CD (Start)` < 720 then '1-2 Years'
when CURRENT_DATE() -`CD (Start)` >= 720 then '2+ years'
else 'Missing' end

But I get the following error message: Column of type date cannot be converted to type floating decimal. Surprising the formula above works in a beast mode. How come it is not working in Magic ETL?

Find more posts tagged with

Magic ETL

Accepted answers

MarkSnodgrass

You should use the DATEDIFF function to get this. You can also simplify your statement due to the way CASE statements process. Here is what I would suggest:

case
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 90 then '3 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 180 then '3-6 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 270 then '6-9 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 360 then '9-12 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 720 then '1-2 Years'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) >= 720 then '2+ years'
else 'Missing' end

All comments

MarkSnodgrass

You should use the DATEDIFF function to get this. You can also simplify your statement due to the way CASE statements process. Here is what I would suggest:

case
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 90 then '3 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 180 then '3-6 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 270 then '6-9 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 360 then '9-12 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 720 then '1-2 Years'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) >= 720 then '2+ years'
else 'Missing' end