Add new columns in MySQL based on dates in table

user28436 · October 2019

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`

ST_Superman · October 2019

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.

ST_Superman · October 2019