Forum Discussion
Shift rota added to SQL script
I have a date calendar that I can connect too but one of the asks I always get is what shift was in that day shift 1 or shift 2.
the rotation is a 14 day pattern and wanted to understand if I can create a number pattern 1-14 and then use that to link the shift for the day
looked as sequence and row_number but I don’t think this helps
was in that day shift 1 or shift 2.And how is shift 1 / 2 defined; we can not guess that?
olafhelper it’s
sun 1
mon 2
tue 2
wed 1
thu 1
fri 2
sat 2
then the next week it alternates and continues in this pattern. So if I could create a 14 day number cycle 1-14 then once 14 is reached repeats I can then use a case statement to add the pattern
apologies if this isn’t making sense
It would help if you could include code and what you expect to see. You can do a sequence to cycle:
DROP SEQUENCE IF EXISTS dbo.fred;
CREATE SEQUENCE dbo.fred
AS TINYINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 14
CYCLE
NO CACHE;DECLARE @i INT = 0
WHILE @i < 100
BEGIN
SELECT NEXT VALUE FOR dbo.fred;SET @i = @i + 1;
END;If "date calendar " is a table with dates, just update that table with the shifts you want it to have (that will also give you the ability to override values later too.
If I am off base, provide some data structures and what the output should be and someone might be able to help
Louis Davidson the table is in the data base which is used across the network not specific for our area so the query is very easy
The tables been created so finance can report using the financial years , financial weeks etc
so the query is as simple as a select * from perfecttable
the only thing I require is to add a shift pattern
