Ambition IT - Weblog

How to calculate working time?

Situation

Say you have a time registration system in your plant that registers when people use their badges to check in and out. And say you want to calculate the actual time that people have been present (and no, the system cannot ‘see’ whether they really work or not).

The problem with these systems is that every check-in and check-out is registered as a separate record in a table. And, of course, not everybody always checks out when he or she leaves the building.

I had to solve this issue for a SQL Server 2000 database. The solution of course works for newer versions as well and I am very interested in other solutions.

Solution

• Determine the time period for which you are calculating the working time, e.g. from 7 AM to 3 PM on Friday, June 2.
• Select all records from the time table. Set the time selection criteria (the WHERE clause) a bit wider than the actual time range you are investigating.
  It means no harm when you select a few hours before 7 AM and a few hours after 3 PM as well; that way you are sure that you include coworkers that clock in before 7 AM and clock out after 3 PM.
  In my solution I had to get the data from possibly multiple tables, since the time system started a new table for every month (that is very inconvenient, but I had to deal with it). Therefor I put the selected records in a table variable @t, but in your situation it may be easier to use a SELECT statement directly from the time table.

/* Declare and determine the start and end time of your time range. */
DECLARE @datStartTimeRange datetime, @datEndTimeRange datetime
SET @datStartTimeRange = '2010-07-02 07:00:00.000'
SET @datEndTimeRange = '2010-07-02 15:00:00.000'

/* Declare a table variable to store the clock / booking records. */
DECLARE @t TABLE (tCoworkerID int, tName varchar(50), tDepID int, tTime datetime)
INSERT INTO @t (tCoworkerID, tName, tDepID, tTime) VALUES (432, 'John', 534, '2010-07-02 06:51:43.000')
INSERT INTO @t (tCoworkerID, tName, tDepID, tTime) VALUES (213, 'Mike', 534, '2010-07-02 07:01:09.000')
INSERT INTO @t (tCoworkerID, tName, tDepID, tTime) VALUES (157, 'Pete', 534, '2010-07-02 07:02:55.000')
INSERT INTO @t (tCoworkerID, tName, tDepID, tTime) VALUES (213, 'Mike', 0, '2010-07-02 15:15:26.000')
INSERT INTO @t (tCoworkerID, tName, tDepID, tTime) VALUES (432, 'John', 0, '2010-07-02 15:15:50.000')
INSERT INTO @t (tCoworkerID, tName, tDepID, tTime) VALUES (157, 'Pete', 0, '2010-07-02 15:16:24.000')

The record set now looks like this; in these sample data a zero in the Department ID field means that the coworker has clocked out.

Combine the records

/* Declare a table variable to store the results of combining the separate clock / booking records into single records. */
DECLARE @r TABLE (rCoworkerID int, rName varchar(100), rStarttime datetime, rDepID int, rEndtime datetime)

INSERT INTO @r (rCoworkerID, rName, rStarttime, rDepID, rEndtime)
SELECT MIN(t1.tCoworkerID),
       MIN(t1.tName),
       MIN(t1.tTime),
       MIN(t1.tDepID),
       ISNULL(MIN(t2.tTime), GETDATE())   -- <-- Use this ISNULL construct when you are calculating working time during a shift
FROM (SELECT * FROM @t) t1
LEFT JOIN (SELECT * FROM @t) t2 on t1.tCoworkerID = t2.tCoworkerID and t2.tTime > t1.tTime
WHERE t1.tDepID <> 0 
GROUP BY t1.tCoworkerID, t1.tTime, t1.tDepID

The trick is in the ‘t2.tTime > t1.tTime’ clause that lets you combine every record in the record set with only newer records in the same record set.

The result looks like this:

Calculate the actual total working time

Now that you have a record set with a start and end working time, you need to determine how long everybody has worked within your time range of 7 AM to 3 PM.

For that you need to take into account that you have selected too many records and that people may have checked out before your time range starts and that people may have checked in after your time range ends.

/* Now combine the results with the time range you are investigating. */
SELECT *,
       DATEDIFF(s, StartTimeCoworkerInShift, EndTimeCoworkerInShift)
FROM (SELECT *,
             CASE WHEN rStarttime < @datStartTimeRange THEN @datStartTimeRange ELSE rStarttime END StartTimeCoworkerInShift,
             CASE WHEN rEndtime > @datEndTimeRange THEN @datEndTimeRange ELSE rEndtime END EndTimeCoworkerInShift
      FROM (SELECT *,
                   CASE WHEN rStarttime > @datEndTimeRange THEN 'Shift starts after time range' END TooLate,
                   CASE WHEN rEndtime < @datStartTimeRange THEN 'Shift ends before time range' END TooEarly
            FROM @r) TooLateOrTooEarly
      WHERE TooLate IS NULL AND TooEarly IS NULL
      ) Times

Of course, any variation and addition in selection criteria is possible. I’m just outlining the solution that I used for this particular problem.

posted @ Friday, July 02, 2010 9:17 AM