beast mode within a beast mode

angela2018
angela2018
in Magic ETL

Hello,

 

I'm creating a card based upon the changes in tenure buckets of our sales reps. I am using start date and "As of" date (or the date we import the roster data. "As of" would be today's date, for instance).

From there, I want to bucket it into different ranges like "0-6 months," 1-2 years," and so on. 

Another piece of this is there are many reps who have terminated. So in cases when there is a termination date present, I want to subtract start date from that date rather than "as of" date.

 

tl;dr : i need to beast mode the actual date difference of start date & today OR term date & start date

then i need to group these into digestible buckets. 

 

This is what I have but I can't figure out how to format it:

 

CASE
when `Termination Date` is not NULL and
(when DATEDIFF(`Termination Date`, `Adjusted Start Date`) <180 then '0-6 Months'
when DATEDIFF(`Termination Date`, `Adjusted Start Date`) >=180 and DATEDIFF(`As Of`, `Adjusted Start Date`) <=365 then '6-12 Months'
when DATEDIFF(`Termination Date`, `Adjusted Start Date`) >365 and DATEDIFF(`As Of`, `Adjusted Start Date`) <=730 then '1-2 Years'
when DATEDIFF(`Termination Date`, `Adjusted Start Date`) > 730 then '2+ Years') else

when `Termination Date`is NULL and
(when DATEDIFF(`As Of`, `Adjusted Start Date`) <180 then '0-6 Months'
when DATEDIFF(`As Of`, `Adjusted Start Date`) >=180 and DATEDIFF(`As Of`, `Adjusted Start Date`) <=365 then '6-12 Months'
when DATEDIFF(`As Of`, `Adjusted Start Date`) >365 and DATEDIFF(`As Of`, `Adjusted Start Date`) <=730 then '1-2 Years'
when DATEDIFF(`As Of`, `Adjusted Start Date`) > 730 then '2+ Years')
END

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Try this:

     

    CASE
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)<180 THEN '0-6 Months'
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)<=365 THEN '6-12 Months'
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)<=730 THEN '1-2 Years'
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)>730 THEN '2+ Years'
    ELSE 'Unknown'
    END

     

    You shouldn't really need the "ELSE 'Unknown'" line.  I like to add it in just in case there are cases that are not included in the other buckets


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

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Try this:

     

    CASE
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)<180 THEN '0-6 Months'
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)<=365 THEN '6-12 Months'
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)<=730 THEN '1-2 Years'
    WHEN DATEDIFF(IFNULL(`Termination Date`,`As Of`),`Adjusted Start Date`)>730 THEN '2+ Years'
    ELSE 'Unknown'
    END

     

    You shouldn't really need the "ELSE 'Unknown'" line.  I like to add it in just in case there are cases that are not included in the other buckets


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

Categories