MYSQL code for Date Ranges and populating new column with fiscal week problems.

user07653

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!

jstan

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

ST_Superman

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`