Split time records across midnight

Question

I'm trying to run some reports and having to deal with the whole issue of employee labor hours crossing midnight. It occurs to me though that I could split the records that cross midnight into two records as if the employee clocked out at midnight and simultaneously clocked back in at midnight thus avoiding the midnight problem altogether.

So if I have:

EmployeeId   InTime                     OutTime
---          -----------------------    -----------------------
1            2012-01-18 19:50:04.437    2012-01-19 03:30:02.433

What do you suppose would be the most elegant way to split this record like so:

EmployeeId   InTime                     OutTime
---          -----------------------    -----------------------
1            2012-01-18 19:50:04.437    2012-01-19 00:00:00.000
1            2012-01-19 00:00:00.000    2012-01-19 03:30:02.433

And yes, I have thoroughly thought through what effects this might have on existing functionality... which is why I'm opting to do this in a temporary table that will not affect existing functionality.

Answer 1

This might help:

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

INSERT INTO @tbl(EmployeeId,InTime,OutTime) VALUES (1,'2012-01-18 19:50:04.437','2012-01-19 03:30:02.433')
INSERT INTO @tbl(EmployeeId,InTime,OutTime) VALUES (2,'2012-01-18 19:50:04.437','2012-01-18 20:30:02.433')
INSERT INTO @tbl(EmployeeID,InTime,OutTime) VALUES (3,'2012-01-18 16:15:00.000','2012-01-19 00:00:00.000')
INSERT INTO @tbl(EmployeeID,InTime,OutTime) VALUES (4,'2012-01-18 00:00:00.000','2012-01-18 08:15:00.000')
SELECT
    tbl.EmployeeId,
    tbl.InTime,
    DATEADD(dd, DATEDIFF(dd, 0, tbl.OutTime), 0) AS OutTime
FROM
    @tbl AS tbl
WHERE
    DATEDIFF(dd,tbl.InTime,tbl.OutTime)=1
UNION ALL
SELECT
    tbl.EmployeeId,
    CASE WHEN DATEDIFF(dd,tbl.InTime,tbl.OutTime)=1
        THEN DATEADD(dd, DATEDIFF(dd, 0, tbl.OutTime), 0)
        ELSE tbl.InTime
    END AS InTime,
    tbl.OutTime
FROM @tbl AS tbl
ORDER BY EmployeeId

Answer 2

The following solution uses a numbers table (in the form of a subset of the master..spt_values system table) to split the time ranges. It can split ranges spanning an arbitrary number of days (up to 2048 with spt_values, but with your own numbers table you can set a different maximum). The specific cases of 1- and 2-day spanning ranges are not addressed here, but I believe the method is lightweight enough for you to try:

;
WITH LaborHours (EmployeeId, InTime, OutTime) AS (
  SELECT
    1,
    CAST('2012-01-18 19:50:04.437' AS datetime),
    CAST('2012-01-18 03:30:02.433' AS datetime)
),
HoursSplit AS (
  SELECT
    h.*,
    SubInTime  = DATEADD(DAY, DATEDIFF(DAY, 0, h.InTime) + v.number + 0, 0),
    SubOutTime = DATEADD(DAY, DATEDIFF(DAY, 0, h.InTime) + v.number + 1, 0)
  FROM LaborHours h
    INNER JOIN master..spt_values v
      ON number BETWEEN 0 AND DATEDIFF(DAY, h.InTime, h.OutTime)
  WHERE v.type = 'P'
),
HoursSubstituted AS (
  SELECT
    EmployeeId,
    InTime  = CASE WHEN InTime  > SubInTime  THEN InTime  ELSE SubInTime  END,
    OutTime = CASE WHEN OutTime < SubOutTime THEN OutTime ELSE SubOutTime END
  FROM HoursSplit
)
SELECT *
FROM HoursSubstituted

Basically, it's a two-step method.

First we use the numbers table to duplicate every row so many times as the number of days the range spans and to prepare ‘standard’ sub-ranges starting at midnight and ending at the next midnight.

Next, we compare the beginning of a sub-range with the beginning of the range to see whether it is the first sub-range, in which case we use InTime as its beginning. Similarly, we compare the endings to see whether we should use OutTime or just the midnight as the end of that subrange.