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'