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.” -Superman
______________________________________________________________________________________________0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有