Generating shift pattern in SQL

Question

I don't know even how to start addressing this issue, any assistance will be appreciated:

My goal is to generate a table (dynamically) that return dates that are working shift.

Settings:

• I have a pattern as follow 7 days on duty 7 days off duty and it goes on...
• I can determine the start date that the shift start
• I can determine the pattern days (7 days - meaning 7 days on 7 days off)
• i can determine the end date for calculating the patern
• I want to calculate and create the following table

For example: Pattern days: 7 Start date: 01/01/2015 Pattern end date: 12/31/2015

ID         StartshiftDate        EndShiftDate     OnDuty
-------------------------------------------------------------
1           01/01/2015           01/07/2015       On Duty
2           01/08/2015           01/14/2015       Off Duty
3           01/15/2015           01/21/2015       On Duty

I know that i need to create CTE that start from the start date, i need to add 7 days for each date. but i don't know how to determine if the range of dates is on duty or off duty.

And how i create the the loop for creating the row till the pattern end date?

Any help will be appreciated

Answer 1

As you mentioned you need to use Recursive CTE. Try this.

DECLARE @Startdate   DATE= '01/01/2015',
        @enddate     DATE = '01/31/2015',
        @Patterndays INT=7;

WITH cte
     AS (SELECT CONVERT(DATE, @Startdate)       [dates],
                1                               AS id,
                CONVERT(VARCHAR(20), 'On Duty') AS duty
         UNION ALL
         SELECT Dateadd(dd, @Patterndays, [dates]),
                ID + 1,
                CASE
                  WHEN ( id + 1 ) % 2 = 1 THEN CONVERT(VARCHAR(20), 'On Duty')
                  ELSE CONVERT(VARCHAR(20), 'Off Duty')
                END
         FROM   cte
         WHERE  dates < Dateadd(dd, -@Patterndays, CONVERT(DATE, @enddate)))
SELECT id,
       dates                AS StartshiftDate,
       Dateadd(DD, 6, dates)EndShiftDate,
       duty
FROM   cte 
Option (maxrecursion 0)

Result :

id  StartshiftDate  EndShiftDate    duty
--  --------------  ------------    -------
1   2015-01-01      2015-01-07      On Duty
2   2015-01-08      2015-01-14      Off Duty
3   2015-01-15      2015-01-21      On Duty
4   2015-01-22      2015-01-28      Off Duty
5   2015-01-29      2015-02-04      On Duty

Answer 2

I solved the problem like that

DECLARE @StartDate DATETIME = '2015-01-01'
DECLARE @EndDate DATETIME = '2015-01-31'
DECLARE @Pattern INT = 14

;WITH 
 N0 AS (SELECT 1 AS n UNION ALL SELECT 7)
,N1 AS (SELECT 1 AS n FROM N0 t1, N0 t2)
,N2 AS (SELECT 1 AS n FROM N1 t1, N1 t2)
,N3 AS (SELECT 1 AS n FROM N2 t1, N2 t2)
,N4 AS (SELECT 1 AS n FROM N3 t1, N3 t2)
,N5 AS (SELECT 1 AS n FROM N4 t1, N4 t2)
,N6 AS (SELECT 1 AS n FROM N5 t1, N5 t2)
,nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS num FROM N6),
ShiftPattern AS (
select
ROW_NUMBER() OVER(ORDER BY num ASC) as ID,
(ROW_NUMBER() OVER(ORDER BY num ASC) % 2) as d,
DATEADD(day, -(@Pattern-1), DATEADD(day, num-1, @StartDate)) as Start,
DATEADD(day, num-1, @StartDate) AS Enddate,
CASE 
WHEN (ROW_NUMBER() OVER(ORDER BY num ASC) % 2) = 1 THEN 'On Duty'
WHEN (ROW_NUMBER() OVER(ORDER BY num ASC) % 2) = 0 THEN 'Off Duty'
END AS 'Duty'
FROM nums
WHERE 
(num % @Pattern) = 0 
AND num <= DATEDIFF(day, @StartDate, @EndDate) + 1)

SELECT *
FROM ShiftPattern

Answer 3

I would use a table of numbers.

http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

This article compares different ways to generate it, including recursive CTE, which is much slower than the rest.

It doesn't really matter how you generate the table of numbers, because normally it is done once. For this example I'll populate a table variable with 10000 numbers using one of the methods from the article above.

declare @TNumbers table (Number int);

INSERT INTO @TNumbers (Number)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

Now we have a table of numbers and generating your dates is a matter of simple formulas:

DECLARE @VarStartDate date = '2015-01-01';
DECLARE @VarEndDate date = '2015-12-31';
DECLARE @VarShiftLength int = 7;

SELECT
    N.Number AS ID
    , DATEADD(day, (N.Number - 1) * @VarShiftLength, @VarStartDate) AS StartShiftDay
    , DATEADD(day, N.Number * @VarShiftLength - 1, @VarStartDate) AS EndShiftDay
    , CASE WHEN N.Number % 2 = 0 THEN 'Off Duty' ELSE 'On Duty' END AS OnDuty
FROM
    @TNumbers AS N
WHERE
    DATEADD(day, N.Number * @VarShiftLength - 1, @VarStartDate) <= @VarEndDate
ORDER BY ID;

Result set:

ID   StartShiftDay   EndShiftDay   OnDuty
1    2015-01-01      2015-01-07    On Duty
2    2015-01-08      2015-01-14    Off Duty
3    2015-01-15      2015-01-21    On Duty
4    2015-01-22      2015-01-28    Off Duty
5    2015-01-29      2015-02-04    On Duty
6    2015-02-05      2015-02-11    Off Duty
7    2015-02-12      2015-02-18    On Duty
8    2015-02-19      2015-02-25    Off Duty
9    2015-02-26      2015-03-04    On Duty
10   2015-03-05      2015-03-11    Off Duty
11   2015-03-12      2015-03-18    On Duty
12   2015-03-19      2015-03-25    Off Duty
13   2015-03-26      2015-04-01    On Duty
14   2015-04-02      2015-04-08    Off Duty
15   2015-04-09      2015-04-15    On Duty
16   2015-04-16      2015-04-22    Off Duty
17   2015-04-23      2015-04-29    On Duty
18   2015-04-30      2015-05-06    Off Duty
19   2015-05-07      2015-05-13    On Duty
20   2015-05-14      2015-05-20    Off Duty
21   2015-05-21      2015-05-27    On Duty
22   2015-05-28      2015-06-03    Off Duty
23   2015-06-04      2015-06-10    On Duty
24   2015-06-11      2015-06-17    Off Duty
25   2015-06-18      2015-06-24    On Duty
26   2015-06-25      2015-07-01    Off Duty
27   2015-07-02      2015-07-08    On Duty
28   2015-07-09      2015-07-15    Off Duty
29   2015-07-16      2015-07-22    On Duty
30   2015-07-23      2015-07-29    Off Duty
31   2015-07-30      2015-08-05    On Duty
32   2015-08-06      2015-08-12    Off Duty
33   2015-08-13      2015-08-19    On Duty
34   2015-08-20      2015-08-26    Off Duty
35   2015-08-27      2015-09-02    On Duty
36   2015-09-03      2015-09-09    Off Duty
37   2015-09-10      2015-09-16    On Duty
38   2015-09-17      2015-09-23    Off Duty
39   2015-09-24      2015-09-30    On Duty
40   2015-10-01      2015-10-07    Off Duty
41   2015-10-08      2015-10-14    On Duty
42   2015-10-15      2015-10-21    Off Duty
43   2015-10-22      2015-10-28    On Duty
44   2015-10-29      2015-11-04    Off Duty
45   2015-11-05      2015-11-11    On Duty
46   2015-11-12      2015-11-18    Off Duty
47   2015-11-19      2015-11-25    On Duty
48   2015-11-26      2015-12-02    Off Duty
49   2015-12-03      2015-12-09    On Duty
50   2015-12-10      2015-12-16    Off Duty
51   2015-12-17      2015-12-23    On Duty
52   2015-12-24      2015-12-30    Off Duty