1

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')

closed as off-topic by Philᵀᴹ, LowlyDBA, Mr.Brownstone, Tom V - Team Monica, John aka hot2use Aug 20 '18 at 8:54

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, LowlyDBA, Mr.Brownstone
If this question can be reworded to fit the rules in the help center, please edit the question.

  • To be completely honest I have no idea where to even start. – Zeena Aug 18 '18 at 21:58
  • Is 10pm and 7am is static. Is that the only criteria for night shift. Can someone be late and come at 11pm and end at 9am? – SqlWorldWide Aug 18 '18 at 22:52
  • Yes they can. So if an employee comes in at 11pm and ends at 9am, only the hours between 11pm to 7am will be be classed as a night shift the remaining time 7am - 9am will be a day shift. – Zeena Aug 18 '18 at 23:01
  • @zeena, 11 pm to 7 am is night and 7 am to 9 pm is the day. what about 9 pm to 11 pm? – Biju jose Aug 19 '18 at 7:30
  • 3
    I'm voting to close this question as off-topic because its crossposted stackoverflow.com/questions/51912989/… – Tom V - Team Monica Aug 20 '18 at 7:25
2

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:

screenshot

  • 1
    I suggest one avoid using the undocumented spt_values table in production code and instead use a user-defined tally table or CTE to generate sequence numbers for common tasks like this. spt_values doesn't exist in Azure SQL Database. – Dan Guzman Aug 19 '18 at 11:21
  • @DanGuzman, good call, I've updated my answer and removed the reference to spt_values. It's also now a bit easier to read. – aduguid Aug 19 '18 at 11:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.