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
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive