WHAT I HAVE CURRENTLY:
I have a table with Customer_ID, Start_Date, Frequency, Day
I have a second table with simply a list of dates throughout the year 2024 and day of the week, like the below…
| Date | Day |
|---|---|
| 01/01/2024 | Monday |
| 02/01/2024 | Tuesday |
| 03/01/2024 | Wednesday |
| 04/01/2024 | Thursday |
| CONTINUE TO END OF 2024 | – |
WHAT I AM TRYING TO DO:
I would like to generate a list of dates, for each customer throughout the year 2024, showing when they are scheduled to have a visit from our service, based on their frequency (this number equates to weeks, so 1 would be every week, 2 would be every 2 weeks etc) from their start date.
For example Table1:
| Customer_ID | Start_Date | Frequency | Day |
|---|---|---|---|
| 001 | 01/01/2024 | 4 | Monday |
| 002 | 04/06/2024 | 2 | Tuesday |
DESIRED RESULT (something like this):
| Customer_ID | Date |
|---|---|
| 001 | 01/01/2024 |
| 001 | 29/01/2024 |
| 001 | 26/02/2024 |
| 001 | 25/03/2024 |
| CONTINUE TO END OF 2024 | – |
| 002 | 04/06/2024 |
| 002 | 18/06/2024 |
| 002 | 02/07/2024 |
| CONTINUE TO END OF 2024 | – |
WHAT I HAVE TRIED / MY IDEAS:
As the minimum frequency a customer can have is 1, I was thinking of
-
generating a list of dates for each customer for each week of the year from their start date
-
then ordering that list with some kind of row function
-
then trying to determine which dates to select from the ordered list?
Open to suggestions…