Split datetime records that fall between 10pm to 7am into new record [closed]

Question

I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from 10pm to 7am.

Example below might help make things clearer.

E.g

EmployeeId   InTime                     OutTime                  
----------   -----------------------    ----------------------- 
1            2018-08-10 21:00:00.000   2018-08-11 09:00:00.000  

Desired Output

    EmployeeId   InTime                     OutTime                   Type
    ----------  -----------------------    -----------------------   -------
    1            2018-08-10 21:00:00.000    2018-08-10 22:00:00.000   day
    1            2018-08-10 22:00:00.000    2018-08-11 07:00:00.000   night
    1            2018-08-11 07:00:00.000    2018-08-11 09:00:00.000   day

code for current table:

DECLARE @tbl TABLE 
    (
        EmployeeId INT,
        InTime DATETIME,
        OutTime DATETIME
    )

INSERT INTO @tbl(Employee_Id,Shift_Start,Shift_end) VALUES (1,'2018-08-10 21:00:00.000','2018-08-11 09:00:00.000')

Answer 1

Try using a recursive Common Table Expression (CTE) to generate a list of hours between the minimum and maximum date/times. Then you can use DENSE_RANK to group the records.

Example:

WITH
source_data
AS
(
    SELECT tbl.* FROM (VALUES
      ( 1, '10-Aug-2018 21:00', '10-Aug-2018 22:00')
    , ( 2, '10-Aug-2018 20:00', '11-Aug-2018 07:00')
    , ( 3, '11-Aug-2018 07:00', '11-Aug-2018 09:00')
    , ( 4, '10-Aug-2018 20:00', '11-Aug-2018 09:00')
    ) tbl ([EmployeeId], [InTime], [OutTime]) 
)
, 
source_data_dates
AS
(
    SELECT [InTimeMin] = MIN([InTime]), [OutTimeMax] = MAX([OutTime]) FROM source_data
)
,
source_data_times([n], [InTime]) 
AS
(
    SELECT 1, CAST([InTimeMin] AS DATETIME) FROM source_data_dates
    UNION ALL
    SELECT n + 1, [InTimeMin] = CAST(DATEADD(hh, n, [InTimeMin]) AS DATETIME)  FROM source_data_times, source_data_dates WHERE n <= DATEDIFF(hh, [InTimeMin], [OutTimeMax])
)
, 
source_data_times_type
AS
(
    SELECT
          [InTime] = [InTime]
        , [OutTime] = DATEADD(hh, 1, [InTime])
        , [Type] = 
            CASE 
                WHEN CAST([InTime] AS TIME) >= '22:00:00' OR CAST([InTime] AS TIME) < '07:00:00'
                THEN 'night'
                ELSE 'day'
            END
    FROM 
        source_data_times
)
, 
source_data_times_type_rank
AS
(
    SELECT 
          sd.[EmployeeId]
        , tt.[InTime]
        , tt.[OutTime]
        , tt.[Type]
        , [GroupRank] = DENSE_RANK() OVER (PARTITION BY sd.[EmployeeId] ORDER BY tt.[Type], CAST(tt.[InTime] AS DATE))
    FROM
        source_data AS sd
        INNER JOIN source_data_times_type AS tt ON tt.[InTime] >= sd.[InTime] AND tt.[InTime] < sd.[OutTime]
)
SELECT
      [EmployeeId]
    , [InTime] = MIN([InTime])
    , [OutTime] = MAX([OutTime])
    , [Type]
FROM 
    source_data_times_type_rank
GROUP BY 
      [EmployeeId]
    , [Type]
    , [GroupRank]
ORDER BY
      [EmployeeId]
    , MIN([InTime])

Results: