I need to produce a shift schedule , it would nee dot be done each year so I want to script it.

I have 4 shifts, each with their own start stop times (generally same pattern) Like A / B is 8am to 8pm (4 days on) and then C / D 8pm to 8am 4 days on) The shifts alternate A/B 4 days and then C / D 4 days .

I know someone has done this but I need help to know how or where to begin with the code. I know I need a table of shifts with their start and stop times, a table of holidays (maybe - I think there might be something built in to do this).

Then I need the actual table of the schedule .. Shift, Start, Stop , Day/Night, Holiday. 4 days on and 4 days off - if I provide a starting date of December 28th complete schedule by month for a year.

How can I achieve that?

I found this gem to do the annualized yearly day by day , I am guessing I need to look up some math to get it to iterate 4 on 4 off and maybe join two tables

CodePudding user response：

I have no idea if this is a good idea for you to use. Nonetheless, here is a script that will generate a list of shifts while skipping over a list of holidays. While plenty of assumptions are built in it should not be terribly difficult to adapt for similar patterns:

with const as (
   select cast('20211228' as date) as basedate, 8 as cycle_length
), holidays as (
    select holiday
    from const cross apply (values
        (datefromparts(year(basedate)   1,  2, 14)),
        (datefromparts(year(basedate)   1,  3, 17)),
        (datefromparts(year(basedate)   1,  7,  4)),
        (datefromparts(year(basedate)   1, 10, 31)) 
    ) as h(holiday)
), numbers as (
    select n
    from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) as n(n)
), cycles as (
    select cycle.n   (n0.n   10 * n1.n) * cycle_length as daynum
    from numbers n0 cross join numbers n1 cross join numbers cycle cross join const
    where cycle.n < cycle_length and cycle.n   (n0.n   10 * n1.n) * cycle_length < 367
)
select schedule_date, description
    from const cross apply cycles cross apply
        (select dateadd(day, daynum, basedate) as dt) d cross apply
        (select count(*) from holidays where holiday <= dt) h(skips) cross apply
        (select n from numbers where n in (1, 2)) s(shift) cross apply
        (values (
                dateadd(day, skips, dt),
                case daynum / (cycle_length / 2) % 2
                    when 0 then case shift when 1 then 'A 8am-8pm' else 'B 8pm-8am' end
                    when 1 then case shift when 1 then 'C 8am-8pm' else 'D 8pm-8am' end
                end
        )) v2(schedule_date, description)
    order by schedule_date;

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1d03e27163bd37484d2d5c288eb9e80c