Add new columns in MySQL based on dates in table

I'm trying to add two new columns to a dataset, each based on certain conditions across 3 existing date columns in the input table. This either needs to be a MySQL or Magic ETL dataflow as it gets used to join with another dataset vs. directly powering a card.

 

Existing Columns:

'Created Date'

'Converted Date'

'Last Interesting Moment Date'

Each of these are date data types in the input dataset, using a format like Jan 1, 2019

 

The two new columns and logic are:

1) 'Active in 2019': if any of the 3 existing dates have a date of Jan 1, 2019 or later

2) 'Latest Active Date': the most recent value for any of the 3 existing dates

 

Here are my attempts in SQL, breaking the above out into two separate table transforms

1) Create the 'Active in 2019' Column:

SELECT
*,
CASE 
WHEN `Created Date` > 'Dec 31, 2018'
OR `Converted Date` > 'Dec 31, 2018'
OR `Last Interesting Moment Date` > 'Dec 31, 2018'
THEN "Yes"
ELSE "No"
END AS 'Active in 2019'

FROM `sf_001a_sfdc_leads_conversions_transforms`

 

2) Create the 'Last Active Date' Column:

SELECT
*,CASE
WHEN `Created Date` >= `Converted Date`
OR `Created Date` >= `Last Interesting Moment Date`
THEN `Created Date`
WHEN `Converted Date` >= `Created Date` OR `Converted Date` >= `Last Interesting Moment Date` THEN `Converted Date`
WHEN `Last Interesting Moment Date` >= `Created Date` AND `Last Interesting Moment Date` >= `Converted Date` THEN `Last Interesting Moment Date`
ELSE `Created Date`
END AS 'Last Active Date'

FROM
`sf_001a_sfdc_leads_conversions_transforms`

Best Answer

  • ST_-Superman-_
    Accepted Answer

    I'll take a stab at this one...

     

    I think I would try this in three transforms.  Then I would join these two fields to the rest of your data by joining on the lead id

     

    Step 1: Ensure that you only have one date for each of the three fields per Lead Id

    SELECT
    `Lead Id`
    ,max(`Created Date`) as `Created Date`
    ,max(`Converted Date`) as `Converted Date`
    ,max(`Last Interesting Moment Date`) as `Last Interesting Moment Date`
    FROM sf_001a_sfdc_leads_conversions_transforms
    GROUP BY `Lead Id`

    I called this table max_dates_sfdc_leads

     

    Step 2: Calculate the Active in 2019 Field and determine the max date of the three other fields

    SELECT
    `Lead Id`
    ,`Created Date`
    ,`Converted Date`
    ,`Last Interesting Moment Date`
    ,CASE
    WHEN YEAR(`Created Date`)=2019 OR
    YEAR(`Converted Date`)=2019 OR
    YEAR(`Last Interesting Moment Date`)=2019 THEN 'Yes'
    ELSE 'No'
    END AS `Active in 2019`
    ,GREATEST(`Created Date`, COALESCE(`Converted Date`,0), COALESCE(`Last Interesting Moment Date`,0)) as `Date Max`
    FROM max_dates_sfdc_leads
    GROUP BY `Lead Id`

    I called this active_in_2019

     

    Step 3: Calculate the second field:

    SELECT
    `Lead Id`
    ,`Active in 2019`
    ,CASE
    WHEN `Converted Date` = `Date Max` then 'Converted Date'
    WHEN `Last Interesting Moment Date` = `Date Max` then 'Last Interesting Moment Date'
    WHEN `Created Date` = `Date Max` then 'Created Date'
    END AS `Last Active Date`
    FROM active_in_2019
    GROUP BY `Lead Id`

     

    You should be left with a table that lists

    Lead Id, Active in 2019, and Last Active Date

     

    Join that to your main table based on Lead Id and you should be set.

     

     

Answers