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.
0
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.
0 -
Worked like a charm! Thanks @ST_-Superman-_ for the quick and helpful reply!
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 816 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 411 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 11 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive