Divide the time after midnight

Question

I am trying to run several reports and deal with the whole problem of the hours of work of employees crossing midnight. It seems to me that I could divide the notes that turn over midnight into two notes, as if the employee went out at midnight and at the same time returned back at midnight, thereby completely avoiding the problem of midnight.

So, if I have:

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

Which, in your opinion, would be the most elegant way to break this entry as follows:

 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 carefully thought out what consequences this could have for existing functions ... that is why I prefer to do this in a temporary table that will not affect existing functions.

Answer 1

This can 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 table of numbers (as a subset of the system table master..spt_values ) to separate time ranges. It can split ranges spanning an arbitrary number of days (up to 2048 with spt_values , but with your own number table you can set a different maximum). The specific cases of 1- and 2-day coverage ranges are not covered here, but I find this method easy 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 

In principle, this is a two-stage method.

First we use a number table to duplicate each row as many times as the number of days during which the range spans and prepares "standard subranges starting at midnight and ending at next midnight."

Next, we compare the beginning of the subband with the beginning of the range to see if it is the first subband, in which case we use InTime as its beginning. Similarly, we compare the endings to see if OutTime should be used or only at midnight as the end of this subband.

Answer 3

If for a report, you should simply be able to make a query / join that give two records within these conditions from the source, starting ... Without SQL-Server 2008, I can only offer pseudo-code query for you.

The first part gets all the records based on any state of your range that you want to show. The value "OutTime" is arbitrary ... if it is on the same day, then there is no cross, just use the exit time. If this happens the next day, use casting to dynamically create the date “YYYY-MM-DD” (which will be 00:00:00 by default), as you want as the OUT time.

UNION will ONLY capture the same records that were registered with FIRST, where I / O dates differ. Thus, we KNOW that we want OutTime to act as InTime, but based on the time “00:00:00”, therefore the exact same castration of the date and time field is performed, and for these records just use the final value “OutTime” as it is.

An additional column for “TimeSplit” from “1” or “2” is to make sure that we can still group the employee ID, but from this make sure that the first records (initial shift) of any of them have the record “2 "to cover the day 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 4

Try this because you can insert from a selection and inside your selection you can set values ​​to be used on different days.

To add a new line:

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

Source line update:

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