MySQL bi-weekly rotating workers shifts-CodePudding

I need to create a view or SP to generate a table with workers' shifts in MySQL.

We have 2 shifts that rotate every week. A worker that has worked this week in a morning shift (06h-14h), next week he will work in a afternoon shift (14h-22h). If I set a fixed day to get a starting point, let's say that on 01/01/2021 Shift A was working a morning shift (06h-14h) and on that day Shift B worked in the afternoon shift (14h-22h).

How to create a view with populated shifts? For example:

01/01/2021 (Sat)   ShiftA   AM
01/01/2021 (Sat)   ShiftB   PM
02/01/2021 (Sun)   ShiftA   AM
02/01/2021 (Sun)   ShiftB   PM
03/01/2021 (Mon)   ShiftA   PM
03/01/2021 (Mon)   ShiftB   AM
04/01/2021 (Tue)   ShiftA   PM
04/01/2021 (Tue)   ShiftB   AM

.......

Could maybe the MySQL Week() function be used here?

CodePudding user response：

You can use a recursive cte to build the schedule: the cte's termination condition can be when the number of weeks the schedule should be is reached, and you can keep a counter along with the original shift assignments and use modulo to reassign shifts in alterating form when a new week is started:

with recursive cte(dt, shift, tm) as (
    select date(now()), 1, 0
    union all
    select date(now()), 2, 1 
    union all
    select c.dt   interval 1 day, c.shift, case when dayofweek(c.dt   interval 1 day) = 2 then c.tm%2 = 0 else c.tm end 
    from cte c where c.dt < now()   interval 2 week
)
select dt, shift, case when tm = 0 then 'AM' else 'PM' end from cte;

See fiddle.