mysql - SQL - Custom Date Groups

Question

really hitting my head on a wall with this one.

I'm basically trying to create week/month groups within SQL, so say a team member started their contract on 05/01/2021, then 05/01/2021 - 12/01/2021 would be week 1, 12/01/2021 - 19/01/2021 would be week 2 etc up until week 26. The purpose is so I can then use these week groups to more easily make comparisons to other team members.

As an example, say I have simple data like this laid out.

Staff_Members	Start Date
team Member 1	25/06/2019
team Member 2	30/06/2019

question from:https://stackoverflow.com/questions/65952719/sql-custom-date-groups

Answer 1

You can use a recursive CTE. In MySQL, this looks like:

with recursive cte as (
      select staff_member, start_date, 1 as week_number, start_date as week_start, start_date + interval 7 day as end_date
      from t
      union all
      select staff_member, start_date, week_number + 1, end_date, end_date + interval 7 day
      from cte
      where year(start_date) = year(week_start)
     )
 select *
 from cte
 order by staff_member, week_start;

Here is a db<>fiddle.