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
-
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.” -Superman0
Answers
-
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.” -Superman0 -
Worked like a charm! Thanks @ST_-Superman-_ for the quick and helpful reply!
0
Categories
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive