Create date ranges list

Question

I have a start date 01/jan and end date 22/jan

My employees work 5 days consecutively and they stay at home 5 days to start to work again after these last 5 days. These 5 days are defined as policy in our company.

How can I get the working days in ranges in SQL Server? Like this

Bob starts to work from 01/jan John starts to work from 06/jan Alex starts to work from 20/jan

Period: January from 01/jan to 22/jan

Bob 01/jan - 05/jan

Bob 11/jan - 15/jan

Bob 21/jan - 22/jan

John 06/jan - 10/jan

John 16/jan - 20/jan

Alex 20/jan - 22/jan

I found this answer from this article but I don't understand the code.

How to generate a range of dates in SQL Server

Answer 1

Here you go! Have fun. If you need any more help just ask.

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--This is a table that holds all the people who work for you and when they started
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
DECLARE @People TABLE
(
    Name        NVARCHAR(64),
    FirstDate   DATE
)

INSERT INTO @People
(
    Name,
    FirstDate
)
VALUES 
    ('Bob','01/jan/2015'),
    ('Alex','20/jan/2015'),
    ('John','6/jan/2015')

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--This is where the magic happens
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

--This is the date range you want the results for
DECLARE @StartDate  DATE = '1/JAN/2015'
DECLARE @EndDate    DATE = '22/JAN/2015'

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--Using the above dates we can make a temporary table with all the dates for this date range
--This table could be a permanent table (with all reasonable dates in) if you wanted it to be,
--but for this example we will make it from scratch each time
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

DECLARE @DateTable TABLE (Date DATE)

DECLARE @Date DATE = @StartDate

--This is just a loop that inserts all the dates in the range
WHILE @Date <= @EndDate
BEGIN
    INSERT INTO @DateTable(Date) VALUES (@Date)
    SET @Date = DATEADD(D,1,@Date)
END

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--Now this is the bit you will be interested in
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

SELECT          Name,                       --Name
                MIN(Date) AS [From],        --First date
                MAX(Date) AS [To]           --Last date
FROM            @People AS P                --All people
CROSS JOIN      @DateTable AS D             --All dates
WHERE           DATEDIFF(D,FirstDate,Date) / 5 % 2 = 0      --Turn dates into groups each with 5 dates in (/5) Then pick only the even groups (%2 = 0) so one on one off
AND             FirstDate <= Date                                   --The date must be after they started
GROUP BY        Name,DATEDIFF(D,FirstDate,Date) / 5             --Group by each group so we can work out the first and last date of each group
ORDER BY        Name                                                        --Make it look nice

-----------------------------------------

If you want to change the number of days on and off, then it gets a bit more complex.

here is how you could do it

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--This is a table that holds all the people who work for you and when they started
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
DECLARE @People TABLE
(
    Name        NVARCHAR(64),
    FirstDate   DATE
)

INSERT INTO @People
(
    Name,
    FirstDate
)
VALUES 
    ('Bob','01/jan/2015'),
    ('Alex','20/jan/2015'),
    ('John','6/jan/2015')

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--This is where the magic happens
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

--This is the date range you want the results for
DECLARE @StartDate  DATE = '1/JAN/2015'
DECLARE @EndDate    DATE = '22/JAN/2015'

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--Using the above dates we can make a temporary table with all the dates for this date range
--This table could be a permanent table (with all reasonable dates in) if you wanted it to be,
--but for this example we will make it from scratch each time
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

DECLARE @DateTable TABLE (Date DATE)

DECLARE @Date DATE = @StartDate

--This is just a loop that inserts all the dates in the range
WHILE @Date <= @EndDate
BEGIN
    INSERT INTO @DateTable(Date) VALUES (@Date)
    SET @Date = DATEADD(D,1,@Date)
END

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--Used for changing the number of days each perosn works vs not works
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

DECLARE  @DaysOn  INT
DECLARE  @DaysOff INT

SET @DaysOn = 5         --Days working
SET @DaysOff = 1        --Days not working

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--Now this is the bit you will be interested in
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

SELECT   Name,
         MIN(Date) AS [From],     --First date
         MAX(Date) AS [To]        --Last date
FROM
(
   SELECT          Name,                        --Name
                   Date,                        --Date
                   ROW_NUMBER() OVER (PARTITION BY Name,DATEDIFF(D,FirstDate,Date) / (@DaysOn + @DaysOff) ORDER BY Date) AS Day,    --Day of cycle
                   DATEDIFF(D,FirstDate,Date) / (@DaysOn + @DaysOff) AS Cycle                                                       --Cycle
   FROM            @People AS P                --All people
   CROSS JOIN      @DateTable AS D             --All dates
   WHERE          FirstDate <= Date                                   --The date must be after they started
) AS Cycle
WHERE Day <= @DaysOn    --Only total up working days
GROUP BY Name,Cycle     --For each person and cycle
ORDER BY Name           --Make it looks nice