Shift rota added to SQL script

Copper Contributor

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 

4 Replies

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 

@Appers100 


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