Add new columns in MySQL based on dates in table

Reply
Highlighted
White Belt

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`


Accepted Solutions
Black Belt

Re: Add new columns in MySQL based on dates in table

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.

 

 


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

View solution in original post


All Replies
Black Belt

Re: Add new columns in MySQL based on dates in table

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.

 

 


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

View solution in original post

White Belt

Re: Add new columns in MySQL based on dates in table

Worked like a charm! Thanks @ST_-Superman-_  for the quick and helpful reply!

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!