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

Building on accepted answer, as a newbie on mysql I expanded the code to better understand each scenario

SELECT
    tbl.EmployeeId,
    Datediff(tbl.OutTime, tbl.InTime) as DD,
    -- Change outTime to end of the day if shift is overnight
    DATE_FORMAT(tbl.InTime, "%Y-%m-%d %H:%i:%s") as InTime,
    CASE WHEN Datediff(tbl.OutTime, tbl.InTime) = 1
        THEN DATE_FORMAT(tbl.OutTime, "%Y-%m-%d 00:00:00")
        ELSE DATE_FORMAT(tbl.OutTime, "%Y-%m-%d %H:%i:%s")
    END AS OutTime    
FROM tbl 
UNION DISTINCT
SELECT
    tbl.EmployeeId,
    Datediff(tbl.OutTime,tbl.InTime) as DD,
    -- Change inTime to beginning of the next day if shift is overnight
    CASE WHEN Datediff(tbl.OutTime,tbl.InTime) = 1
        THEN DATE_FORMAT(tbl.OutTime, "%Y-%m-%d 00:00:00")
        ELSE DATE_FORMAT(tbl.InTime, "%Y-%m-%d %H:%i:%s")
    END AS InTime,
    DATE_FORMAT(tbl.OutTime, "%Y-%m-%d %H:%i:%s") as OutTime
FROM tbl
order by EmployeeId

Answer 2

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 3

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.

Answer 4

If for the report, then you should just be able to do a query / union that give two records during those conditions from the original one starting... Without having SQL-Server 2008, I can only offer a pseudo-code query for you.

The first part of the gets all records based on whatever your range condition to show. The value of the "OutTime" is conditional... if its on the same day, then no cross over, just use the out time. If it IS on the next day, use casting to dynamically build out a 'YYYY-MM-DD' date (which will default to 00:00:00 time) as you want as the OUT time.

The UNION will ONLY grab those same records qualified in the FIRST where the in/out dates are DIFFERENT. As such, we KNOW we want whatever the OutTime was to act as the InTime, but based on the "00:00:00" time, so the exact same casting of a date/time field is performed, and for these records, just use the final "OutTime" value as-is.

The extra column for "TimeSplit" of '1' or '2' is to make sure that we can still group by employee ID, but from that, ensure that the '1' entries (starting shift) are first, followed by any for the respective same person have a '2' entry for the day overlap in their shift.

select
      tc.EmployeeID,
      '1' as TimeSplit,
      tc.InTime,
      case when datepart( dd, tc.InTime ) = datepart( dd, tc.OutTime )
         then tc.OutTime 
         else CAST( CAST( datepart(yyyy, tc.OutTime ) AS varchar) 
              +'-'+ CAST( datepart( mm, tc.OutTime ) AS varchar) 
              +'-'+ CAST( datepart( dd, tc.OutTime ) AS varchar) AS DATETIME)
         end as OutTime
   from 
      TimeCard tc
   where
      YourDateRangeConditions...
   ORDER BY
      tc.EmployeeID, 
      TimeSplit
UNION ALL
select
      tc.EmployeeID,
      '2' as TimeSplit,
      CAST(    CAST( datepart(yyyy, tc.OutTime ) AS varchar) 
         +'-'+ CAST( datepart( mm, tc.OutTime ) AS varchar) 
         +'-'+ CAST( datepart( dd, tc.OutTime ) AS varchar) AS DATETIME)
         end as InTime
      tc.OutTime
   from 
      TimeCard tc
   where
      YourDateRangeConditions...
      AND NOT datepart( dd, tc.InTime ) = datepart( dd, tc.OutTime )

Answer 5

Try this, because you can do an insert off of a select and inside your select you can set the values to use to be different days.

For Adding the new row:

insert into table ("EMPLOYEE_ID","INTIME","OUTTIME") values
SELECT EMPLOYEE_ID,date(INTIME),OUTTIME
FROM table
where date(intime) < date(outtime)

Updating the original row:

update table 
set outtime =date(outtime) 
where date(intime)= date(outtime)