## SQL – Overlapping Transactions, How Many Hours have We Worked?

19 03 2010

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.