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