SQL - Overlapping Transactions, How Many Hours have We Worked?

March 19, 2010

A couple of years ago I received an email from an ERP mailing list that requested assistance with a task of determining how many hours an employee was in a building.  The ERP software allows employees to log into the computer system that job A was in-process between 8:00 AM and 10:00 AM and job B was in-process from 10:00 AM to noon, so in this case it would be a simple task of subtracting the corresponding starting time from the ending time and summing the results.  But what happens if the employee is responsible for monitoring automated processes, where the employee may monitor several simultaneous in-process jobs?  For example, job A was in-process between 8:00 AM and 10:00 AM and job B was in-process from 8:30 AM to 9:25 AM, job C was in-process from 9:00 AM to 11:00 AM, job D was in-process from 10:30 AM to noon.  In the case of the concurrent in-process jobs it becomes much more difficult to show that the employee worked for 4 hours, just as in the first example.

How could we solve this problem?  Paint a picture – there are 1440 minutes in a day, so we could just lay down each of the transactions on top of each of those minutes to see how many of the minutes are occupied.  If we start with the following, we obtain the number of minutes past midnight of the shift date for the clock in and clock out, rounded to the nearest minute:

SELECT
  EMPLOYEE_ID,
  SHIFT_DATE,
  ROUND((CLOCK_IN-SHIFT_DATE)*1440) CLOCK_IN,
  ROUND((CLOCK_OUT-SHIFT_DATE)*1440) CLOCK_OUT
FROM
  LABOR_TICKET
WHERE
  SHIFT_DATE=TRUNC(SYSDATE-1);

If we had a way of stepping through all of the minutes in the day, and potentially two days in the event that the shift date crosses midnight, we could count the number of minutes in which there was an active labor ticket for each employee.  To do this, we need a counter:

SELECT
  ROWNUM COUNTER
FROM
  DUAL
CONNECT BY
  LEVEL<=2880;

Now, if we combine the two SQL statements together, placing each in an inline view, we can count the number of distinct minutes for which there was a labor ticket:

SELECT
  LT.EMPLOYEE_ID,
  LT.SHIFT_DATE,
  MIN(CLOCK_IN) CLOCK_IN,
  MAX(CLOCK_OUT) CLOCK_OUT,
  COUNT(DISTINCT C.COUNTER)/60 HOURS
FROM
  (SELECT
    EMPLOYEE_ID,
    SHIFT_DATE,
    ROUND((ROUND(CLOCK_IN,'MI')-SHIFT_DATE)*1440) CLOCK_IN,
    ROUND((ROUND(CLOCK_OUT,'MI')-SHIFT_DATE)*1440) CLOCK_OUT
  FROM
    LABOR_TICKET
  WHERE
    SHIFT_DATE=TRUNC(SYSDATE-1)) LT,
  (SELECT
    ROWNUM COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=2880) C
WHERE
  C.COUNTER>LT.CLOCK_IN
  AND C.COUNTER<=LT.CLOCK_OUT
GROUP BY
  LT.EMPLOYEE_ID,
  LT.SHIFT_DATE;

We have a couple of potential problems with the above:

1. It could bury/peg the CPU in the database server due to the CONNECT BY.
2. The CLOCK_IN and CLOCK_OUT times are not in a friendly format.

First, we create a counter table:

CREATE TABLE
  MY_COUNTER
AS
SELECT
  ROWNUM COUNTER
FROM
  DUAL
CONNECT BY
  LEVEL<=2880;

COMMIT;

Now, plugging in the counter table and cleaning up the CLOCK_IN and CLOCK_OUT times:

SELECT
  LT.EMPLOYEE_ID,
  LT.SHIFT_DATE,
  MIN(CLOCK_IN)/1440+SHIFT_DATE CLOCK_IN,
  MAX(CLOCK_OUT)/1440+SHIFT_DATE CLOCK_OUT,
  ROUND(COUNT(DISTINCT C.COUNTER)/60,2) HOURS
FROM
  (SELECT
    EMPLOYEE_ID,
    SHIFT_DATE,
    ROUND((ROUND(CLOCK_IN,'MI')-SHIFT_DATE)*1440) CLOCK_IN,
    ROUND((ROUND(CLOCK_OUT,'MI')-SHIFT_DATE)*1440) CLOCK_OUT
  FROM
    LABOR_TICKET
  WHERE
    SHIFT_DATE=TRUNC(SYSDATE-1)) LT,
  (SELECT
    COUNTER
  FROM
    MY_COUNTER) C
WHERE
  C.COUNTER>LT.CLOCK_IN
  AND C.COUNTER<=LT.CLOCK_OUT
GROUP BY
  LT.EMPLOYEE_ID,
  LT.SHIFT_DATE;

This solution was just as easy as painting by number.  It is just a matter of breaking a complicated task into solvable problems.