MYSQL code for Date Ranges and populating new column with fiscal week problems.
I have two data sets. The first data set is full of leads/opportunities (FY18 MQLs) and I want to automate populating a column with the fiscal week it was created. For example: 'Created Date' = '10/02/2017' Would be between 10/01/2017 and 10/05/2017 (Q1W1).. Our reporting weeks are from Friday to the following Thursday, Instead of the typical 7 day week. The second data (Fiscal Week Ranges) set I am using is a raw excel file uploaded with the following columns: 'Fiscal Week', 'Start Date', 'End Date'.
I want the the code to look up the created date of the opportunity in the FY18MQLs file then based on what fiscal week that date falls in, the new column would populate with the fiscal week for that opportunity. I know the following code is incorrect, but I don't know how to use the first data set to reference the second data set to populate the new column.
WHEN 'Created Date' BETWEEN 'Start Date' AND 'End Date' THEN 'Fiscal Week'
Thanks!
Comments
-
Hi, I use redshift datasets, so my code might a little adaptaption. You will need to use the BETWEEN in the join.
SELECT
*
,"Fiscal Week"
FROM "FY18 MQLs"
LEFT JOIN "Fiscal Week Ranges" ON "Created Date" BETWEEN "Start Date" AND "End Date"
Second option is a case statement that adds a week to Friday & Saturday. Below is a beast mode I built to test it. Something similar would also work in a dataflow.
WEEK(`fiscal_date`) + (CASE WHEN DAYOFWEEK(`fiscal_date`) IN(6,7) THEN 1 ELSE 0 END)
Hope this helps.
Thanks,
Josh
0 -
SELECT
fy.`Created Date`
,wr.`Fiscal Week`
FROM `FY18 MQLs` fy
LEFT JOIN `Fiscal Week Ranges` wr
WHERE fy.`Created Date`>=wr.`Start Date` AND fy.`Created Date`<=wr.`End Date`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive