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:
- It could bury/peg the CPU in the database server due to the CONNECT BY.
- 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.
How would this work in Microsoft Server SQL without using tables?
Tanya,
I am not that familiar with SQL Server, so I do not know if there is a method to dynamically create rows as is possible with Oracle’s CONNECT BY LEVEL syntax. Maybe another reader knows a method – maybe using a T-SQL to read the data and then load it into an array in memory?
If you did set up a table, SQL Server should be able to perform the comparisons. It supports inline views (although it is apparently necessary to always alias those inline view), and the SELECT COUNT(DISTINCT ) syntax:
http://msdn.microsoft.com/en-us/library/ms175997.aspx