Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to get a list of dates, for each customer, given a single date and a frequency SQL

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…

Tags:

2

Answers


  1. 0

    Try this –

    WITH RECURSIVE CustomerDates AS (
SELECT 
    Customer_ID,
    Start_Date AS Date
FROM 
    CustomerSchedule

UNION ALL

SELECT 
    c.Customer_ID,
    DATEADD(week, cs.Frequency, c.Date) AS Date
FROM 
    CustomerDates c
    JOIN CustomerSchedule cs ON c.Customer_ID = cs.Customer_ID
WHERE 
    DATEADD(week, cs.Frequency, c.Date) <= '2024-12-31'
)

SELECT 
cd.Customer_ID, 
cd.Date FROM CustomerDates cd
    JOIN Dates2024 d ON cd.Date = d.Date
    JOIN CustomerSchedule cs ON cd.Customer_ID = cs.Customer_ID
    WHERE d.Day = cs.Day
ORDER BY cd.Customer_ID, cd.Date;

    Explanation

    1. The base query selects the initial Start_Date for each customer.
    2. The recursive part adds the next date by adding the Frequency in weeks to the current date.
    3. This recursion continues until the date exceeds the end of 2024 (2024-12-31).

    Final Select:

    1. Joins CustomerDates with Dates2024 to ensure the dates are within 2024 and match the corresponding day of the week specified for each customer.
    2. Orders the results by Customer_ID and Date.

    This would give you your required output. Cheers.

    Login or Signup to reply.
  2. 0

    Assuming the start_date is always on the specified day, you can simply join your Customers table with the Dates table on a condition that the number of days between the customer’s start_date and the date is an exact multiple of your desired frequency. As the frequency seems to be in weeks, you have to multiply it by 7 to get the frequency days …

    select c.customerid, d.date
from customers c inner join dates d 
    on c.start_date <= d.date and
       mod(datediff(d.date, c.start_date), c.frequency * 7)) = 0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search