SQL repeat rota pattern for year

Transact-SQL
1

Hi all

This is my first post so apologies about formatting tags - please advise where the help page is showing the tags for formatting sql etc.

I have a rota pattern that is 21 days long. To keep it simple the query is in this format

Id - Pos
100 - 1
100 - 2
100 - 3
.... and so on until
100 - 21

This SQL works fine for selecting one pattern

SELECT runSql.[Id], c.[Position]
FROM dbo.[Pattern] AS runSql
INNER JOIN dbo.[Cycles] AS c ON c.[Id] = runSql.[Id]
WHERE runSql.[Id] = '100'

What I want to do is repeat this pattern for the entire year, I've seen examples using CTE I think but no idea where to start with my example.

I also want to do this from a specific starting position in the pattern based on 1/4/16.

I'll try an explain...

In this example Pos 1 = 28/03/2016, so the 1/4/2016 would actually be position 5.

So what I want to do is select from this query from the 5th row. it would then repeat 17 times fully (17*21 days=357) and then have a remaining 8 days making the 365 days.

so the result would be

Date Id Pos
01/04/2016 100 5
02/04/2016 100 6
03/04/2016 100 7
......
29/03/2017 100 10
30/03/2017 100 11
31/03/2017 100 12

I hope this makes sense and hopefully it is possible.

Appreciate any help

Many thanks

2

how do you determine Pos 1 is 28/03/2016 ?

what does [Cycles] table contains ?

what will be the input to the query ? Date ? Pos ?

use the </> button on top of the reply window

3

Hi

Thanks, will use that in future.

I'd made and error with the 28/3/16 - pos 5, it should be 27/03 and the 1/4 is Pos 6.

Pos 1 is always a Sunday and in this case is the w/c of the 1/4/16. In theory, this will then repeat forever year after year.

People have different starting points, i.e, person A starts on day 6 and person B starts 1 week further on in the cycle, but didn't want to complicate my question (which I probably have done now :frowning: )
The cycles table contains and Id, Position, and the ShiftTimesId (which then gets the description from a shift times table - but I've removed all that to keep it simple as possible)

Thanks

4

Hi

The inputs will be the start date and the day position.

1/4/16 = 6 for person A
1/4/16 = 13 for person B

5

can you post the DDL for table Pattern and Cycles ? only the related / required columns. Don't need the full schema

Also same sample data from these 2 tables

6

Few things i assumed

  • the no of cycles is fixed = 21

    create table #Pattern
    (
    Id int
    )

    create table #Cycles
    (
    Id int,
    Position int
    )

    -- Sample Data
    INSERT INTO #Pattern VALUES (100)
    INSERT INTO #Cycles VALUES (100, 1), (100, 2), (100, 3), (100, 4), (100, 5),
    (100, 6), (100, 7), (100, 8), (100, 9), (100, 10),
    (100, 11), (100, 12), (100, 13), (100, 14), (100, 15),
    (100, 16), (100, 17), (100, 18), (100, 19), (100, 20),
    (100, 21)

    DECLARE @start_date datetime,
    @start_pos int

    -- Input
    SELECT @start_date = '2016-04-01',
    @start_pos = 6

    -- Query
    ; WITH
    Num AS
    (
    SELECT n = 0
    union all
    SELECT n = n + 1
    FROM Num
    WHERE n < 100
    ),
    Seq AS
    (
    SELECT [Seq] = row_number() over(order by n, c.[Position]),
    [Pos] = (@start_pos + row_number() over(order by n, c.[Position]) - 2) % 21 + 1
    FROM dbo.[#Pattern] AS runSql
    INNER JOIN dbo.[#Cycles] AS c ON c.[Id] = runSql.[Id]
    INNER JOIN Num AS n ON n.n <= (365 / 21)
    WHERE runSql.[Id] = 100
    )
    SELECT [Date] = dateadd(day, Seq - 1, @start_date),
    [Pos]
    FROM Seq
    WHERE Seq <= datediff(day, @start_date, dateadd(year, 1, @start_date))

    DROP TABLE #Pattern
    DROP TABLE #Cycles

By the way, this is how you should post your schema / table structure and sample data. So that it is ready to use

1 Like
7

Hi

Wow, I seen the first replay whilst at work and I was gonna ask how to give you the information.

Thank you so much for your help, That is perfect.:grin:

The pattern length can change for other patterns, but in the Pattern table there is a field that specifies the days in the pattern.

I've added a bit more into the code. Had a little trouble creating another inner join to the shifts table but I think I got there with it.

Here is the code that now show the 'Shift' associated with a given day. There are also 2 patterns, 100 and 101 which are specified by a variable.

Any feedback welcome.

create table #Pattern
(
    Id        int,
	DaysInPattern	int
)

create table #Cycles
(
    Id        int,
    Position    int,
	ShiftId		int
)

create table #Shifts
(
    Id        int,
	ShiftName	nvarchar(50)
)
-- Sample Data
INSERT INTO #Pattern VALUES (100, 21), (101, 14)
INSERT INTO #Cycles  VALUES
				(100, 1, 1), (100, 2, 2), (100, 3, 2), (100, 4, 2), (100, 5, 2), (100, 6, 2), (100, 7, 1),
				(100, 8, 1), (100, 9, 2), (100, 10, 2), (100, 11, 1), (100, 12, 2), (100, 13, 2), (100, 14, 2),
				(100, 15, 2), (100, 16, 1), (100, 17, 2), (100, 18, 2), (100, 19, 2), (100, 20, 1), (100, 21, 2),
				(101, 1, 1), (101, 2, 3), (101, 3, 3), (101, 4, 3), (101, 5, 3), (101, 6, 3), (101, 7, 1),
				(101, 8, 1), (101, 9, 4), (101, 10, 4), (101, 11, 4), (101, 12, 4), (101, 13, 4), (101, 14, 1)

INSERT INTO #Shifts  VALUES (1, 'OFF'), (2, '09:00-17:00'), (3, '06:00-14:00'), (4, '22:00-06:00')

DECLARE   @start_date    datetime,
          @start_pos    int,
		  @pattern		nvarchar(50)
-- Input
SELECT    @start_date   = '2016-04-01',
          @start_pos    = 6,
		  @pattern		= 101

-- Query
; WITH 
Num AS
(
    SELECT    n = 0
    union all
    SELECT    n = n + 1
    FROM    Num
    WHERE    n < 100
),
Seq AS
(
    SELECT   [Seq]    = row_number() over(order by n, c.[Position]),
             [Pos]    = (@start_pos + row_number() over(order by n, c.[Position]) - 2) % [DaysInPattern] + 1
    FROM     dbo.[#Pattern] AS runSql
             INNER JOIN dbo.[#Cycles] AS c ON c.[Id] = runSql.[Id]
             INNER JOIN Num AS n ON n.n <= (365 / runSql.[DaysInPattern])
    WHERE    runSql.[Id] = @pattern
)
SELECT  [Date] = dateadd(day, Seq - 1, @start_date),
        [Pos],
		c.[ShiftId], s.[ShiftName]
FROM    Seq
	INNER JOIN dbo.[#Cycles] AS c ON c.[Position] = [Pos] AND c.[Id] = @pattern
	INNER JOIN dbo.[#Shifts] AS s on s.[Id] = c.[ShiftId]
WHERE   Seq    <= datediff(day, @start_date, dateadd(year, 1, @start_date))

DROP TABLE #Pattern
DROP TABLE #Cycles
DROP TABLE #Shifts

Once again, can't thank you enough for helping with this. Excellent solution.

8

Hi, what does this < 100 mean. Does this number need to change at all?

9

in the num cte is creating a table with n = 1, 2, 3, .. . 100 is the max.

if you have only 14 or 21 days pattern, the max n you will need is (365 / 14) + 1 . I was lazy to calculate that and so must assumed 100 is the max. You can tune to your requirement. You could also create a permanent tally table instead of using a recursive cte.

Take a look here
The "Numbers" or "Tally" Table