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`
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive