February 15, 2011
There is an interesting problem in the ERP platform that I support. When production floor workers begin working on a particular job, the employee “clocks into” the job in the computer system. When production floor workers stop working on a particular job, the employee “clock out of” the job in the computer system. If the employee is simultaneously operating multiple production floor machines (might be feeding parts into robots or automatic CNC machines) we need to capture the full machine burden rate for all of the machines, but we need to fairly spread the employee’s labor burden rate among the different simultaneously in-process jobs. Assume that the employee is operating 4 production floor machines, and in a simplied example, the employee runs machine 1 between periods 1 and 4, runs machine 2 between periods 4 and 8, machine 3 between periods 6 and 10, and machine 4 between periods 8 and 13 – as depicted below:
How much of the employee’s labor burden should go to the jobs’ transactions on machines 1, 2, 3, and 4? 25% (0.25)? No, because that would only be correct if all of the machines were in use for the entire time duration between periods 1 and 13.
Machine 1/transaction 1:
Quick Description: 3 time periods where the employee’s labor burden should be fully applied, and 1 time period where half of the employee’s labor burden should be applied due to the overlap with machine 2/transaction 2. Therefore, the employee’s labor burden time should be 3 + 1/2 = 3.5 time periods. We can calculate that as a percentage of the total duration as 3.5/4 = 0.875 (87.5%), and save that percentage in a table column, MULTIPLIER_2 in the case of the ERP system.
Machine 2/transaction 2:
Quick Description: 1 time period where the employee’s labor burden should be half applied, 1 time period where the employee’s labor burden should be fully applied, 2 time periods where the employee’s labor burden should be half applied, and 1 time period where the employee’s labor burden should be one third applied. Therefore, the employee’s labor burden time should be 1/2 + 1 + 2/2 + 1/3 = 2.833333. Calculated as a percentage of the total duration we have 2.833333/5 = 0.566667 (56.6%).
Machine 3/transaction 3:
Quick Description: 2 time periods where the employee’s labor burden should be half applied, 1 time period where the where the employee’s labor burden should be one third applied, and 2 time periods where the employee’s labor burden should be half applied. Therefore, the employee’s labor burden time should be 2/2 + 1/3 + 2/2 = 2.333333. Calculated as a percentage of the total duration we have 2.333333/5 = 0.466667 (46.6%).
Machine 4/transaction 4:
Quick Description: 1 time period where the where the employee’s labor burden should be one third applied, 2 time periods where the employee’s labor burden should be half applied, and 3 time periods where the employee’s labor burden should be fully applied. Therefore, the employee’s labor burden time should be 1/3 + 2/2 + 3 = 4.333333. Calculated as a percentage of the total duration we have 4.333333/6 = 0.7222222 (72.2%).
The above is obviously an over-simplification of the calculations, but at least the idea of what needs to happen should be clear when performing the calculations using actual transaction start and end times. Let’s take a look at a more complicated example. Assume that there are a total of 3 simultaneous in-process machine transactions that are started and ended as follows:
Clock In 8:44:01 Start Job 1 8:47:58 Start Job 2 8:57:58 Start Job 3 9:07:58 End Job 2 9:27:58 End Job 1 9:40:00 End Job 3 9:50:00
We can layout a picture in a spreadsheet that shows how the labor burden hours (identified as Hours_Worked) and the calculated percentage of total burden (identified as Multiplier_2) could be computed as the transactions are started and ended:
Of course the above is of limited use without the ability to see the formulas behind the calculations, so I will instruct the spreadsheet program to show the formulas for the cells:
Enough fun with a spreadsheet for now, time to switch to SQL*Plus and Oracle Database to see if we are able to perform the calculations using just a SQL statement. Let’s create a table for demonstration purposes:
CREATE TABLE T1 ( TRANSACTION_ID NUMBER, EMPLOYEE_ID VARCHAR2(15), SHIFT_DATE DATE, CLOCK_IN DATE, CLOCK_OUT DATE, HOURS_WORKED NUMBER(12,2), HOURS_PREVIOUS NUMBER(12,2), HOURS_OVERALL NUMBER(12,2), MULTIPLIER_2 NUMBER(4,3), PRIMARY KEY (TRANSACTION_ID));
Now let’s insert 140 rows into the table, set a random time for the CLOCK_IN column that is within the first 12 hours of the day, set the CLOCK_OUT column value up to four hours after the CLOCK_IN time, and then store the calculated number of hours between the CLOCK_IN and CLOCK_OUT times:
INSERT INTO T1 ( TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE) SELECT ROWNUM TRANSACTION_ID, DECODE(TRUNC((ROWNUM-1)/10), 0, 'ABE', 1, 'BOB', 2, 'CARL', 3, 'DOUG', 4, 'ED', 5, 'FRANK', 6, 'GREG') EMPLOYEE_ID, TRUNC(SYSDATE) SHIFT_DATE FROM DUAL CONNECT BY LEVEL<=70; INSERT INTO T1 ( TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE) SELECT ROWNUM+100 TRANSACTION_ID, DECODE(TRUNC((ROWNUM-1)/10), 0, 'ABE', 1, 'BOB', 2, 'CARL', 3, 'DOUG', 4, 'ED', 5, 'FRANK', 6, 'GREG') EMPLOYEE_ID, TRUNC(SYSDATE+1) SHIFT_DATE FROM DUAL CONNECT BY LEVEL<=70; UPDATE T1 SET CLOCK_IN=SHIFT_DATE+TRUNC(DBMS_RANDOM.VALUE(0,43200))/86400; UPDATE T1 SET CLOCK_OUT=CLOCK_IN+TRUNC(DBMS_RANDOM.VALUE(0,14400))/86400; UPDATE T1 SET HOURS_WORKED=(CLOCK_OUT-CLOCK_IN)*24, HOURS_OVERALL=(CLOCK_OUT-CLOCK_IN)*24; COMMIT;
Unfortunately, calculating the MULTIPLIER_2 value for each transaction will be a bit challenging, because we must consider all of the other transactions that were in-process during each of the transactions. Let’s see what we have so far for just one of the employees:
SELECT TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, TO_CHAR(CLOCK_IN,'HH24:MI:SS') CLOCK_IN, TO_CHAR(CLOCK_OUT,'HH24:MI:SS') CLOCK_OUT, HOURS_WORKED FROM T1 WHERE EMPLOYEE_ID='GREG' ORDER BY SHIFT_DATE, CLOCK_IN; TRANSACTION_ID EMPLOYEE_ID SHIFT_DAT CLOCK_IN CLOCK_OU HOURS_WORKED -------------- ----------- --------- -------- -------- ------------ 64 GREG 13-FEB-11 00:57:31 04:56:33 3.98 63 GREG 13-FEB-11 02:12:04 04:45:36 2.56 61 GREG 13-FEB-11 03:05:34 06:53:24 3.80 66 GREG 13-FEB-11 03:08:21 03:15:04 0.11 70 GREG 13-FEB-11 03:58:45 04:08:28 0.16 62 GREG 13-FEB-11 05:24:03 07:09:41 1.76 69 GREG 13-FEB-11 06:04:48 09:22:00 3.29 67 GREG 13-FEB-11 07:41:20 09:07:06 1.43 65 GREG 13-FEB-11 09:17:35 10:24:15 1.11 68 GREG 13-FEB-11 10:27:03 14:03:30 3.61 161 GREG 14-FEB-11 01:15:40 02:24:01 1.14 169 GREG 14-FEB-11 01:16:01 03:45:38 2.49 166 GREG 14-FEB-11 01:53:02 03:54:09 2.02 163 GREG 14-FEB-11 03:47:15 06:55:34 3.14 170 GREG 14-FEB-11 05:00:19 08:16:00 3.26 165 GREG 14-FEB-11 06:23:45 07:56:40 1.55 162 GREG 14-FEB-11 06:26:06 09:54:15 3.47 168 GREG 14-FEB-11 06:33:39 08:10:56 1.62 167 GREG 14-FEB-11 08:25:00 12:20:55 3.93 164 GREG 14-FEB-11 11:18:45 13:25:08 2.11
Assume that the above is the starting point – the machine transactions (known as labor tickets in the ERP package) exist in the database, even though I have not yet calculated the MULTIPLIER_2 VALUES. Let’s determine the MULTIPLIER_2 values – first we need a way to determine which labor tickets overlap with each of the above labor tickets, and by how much they overlap. We will use a self-join of the test table T1 (which simulates the LABOR_TICKET table in the ERP package):
SELECT L1.TRANSACTION_ID, L1.EMPLOYEE_ID, L1.SHIFT_DATE, TO_CHAR(L1.CLOCK_IN,'HH24:MI') CLOCK_IN, TO_CHAR(L1.CLOCK_OUT,'HH24:MI') CLOCK_OUT, TO_CHAR(L2.CLOCK_IN,'HH24:MI') CLOCK_IN, TO_CHAR(L2.CLOCK_OUT,'HH24:MI') CLOCK_OUT, TO_CHAR(DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN),'HH24:MI') CLOCK_IN, TO_CHAR(DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT),'HH24:MI') CLOCK_OUT, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400) AND L1.EMPLOYEE_ID='GREG' ORDER BY L1.SHIFT_DATE, L1.CLOCK_IN; TRANSACTION_ID EMPLOYEE_ID SHIFT_DAT CLOCK CLOCK CLOCK CLOCK CLOCK CLOCK OVERLAP HOURS_WORKED -------------- ----------- --------- ----- ----- ----- ----- ----- ----- ------- ------------ 64 GREG 13-FEB-11 00:57 04:56 02:12 04:45 02:12 04:45 2.56 3.98 64 GREG 13-FEB-11 00:57 04:56 03:08 03:15 03:08 03:15 0.11 3.98 64 GREG 13-FEB-11 00:57 04:56 00:57 04:56 00:57 04:56 3.98 3.98 64 GREG 13-FEB-11 00:57 04:56 03:05 06:53 03:05 04:56 1.85 3.98 64 GREG 13-FEB-11 00:57 04:56 03:58 04:08 03:58 04:08 0.16 3.98 63 GREG 13-FEB-11 02:12 04:45 03:05 06:53 03:05 04:45 1.67 2.56 63 GREG 13-FEB-11 02:12 04:45 00:57 04:56 02:12 04:45 2.56 2.56 63 GREG 13-FEB-11 02:12 04:45 03:58 04:08 03:58 04:08 0.16 2.56 63 GREG 13-FEB-11 02:12 04:45 03:08 03:15 03:08 03:15 0.11 2.56 63 GREG 13-FEB-11 02:12 04:45 02:12 04:45 02:12 04:45 2.56 2.56 61 GREG 13-FEB-11 03:05 06:53 03:58 04:08 03:58 04:08 0.16 3.80 61 GREG 13-FEB-11 03:05 06:53 03:08 03:15 03:08 03:15 0.11 3.80 61 GREG 13-FEB-11 03:05 06:53 06:04 09:22 06:04 06:53 0.81 3.80 61 GREG 13-FEB-11 03:05 06:53 03:05 06:53 03:05 06:53 3.80 3.80 61 GREG 13-FEB-11 03:05 06:53 02:12 04:45 03:05 04:45 1.67 3.80 61 GREG 13-FEB-11 03:05 06:53 00:57 04:56 03:05 04:56 1.85 3.80 61 GREG 13-FEB-11 03:05 06:53 05:24 07:09 05:24 06:53 1.49 3.80 66 GREG 13-FEB-11 03:08 03:15 03:05 06:53 03:08 03:15 0.11 0.11 ...
In the above, the first “CLOCK” column shows the CLOCK_IN for our transaction, and the second “CLOCK” column shows the CLOCK_OUT for our transaction. The next two “CLOCK” columns show the CLOCK_IN and CLOCK_OUT for an overlapping transaction. If the CLOCK_IN for the transaction is after than the CLOCK_IN for the overlapping transaction, then the CLOCK_IN for our transaction is listed in the second to last “CLOCK” column, otherwise the CLOCK_IN for the overlapping transactions is displayed in the second to last “CLOCK” column. The reverse is true for the last “CLOCK” column, where the earliest of the CLOCK_OUT for the two transactions is displayed. The OVERLAP column shows the number of hours difference between the last two “CLOCK” columns – that shows the number of hours the two transactions have in common.
Now the last step to calculate the MULTIPLIER_2 (labor burden hours as a percentage of the machine burden hours), find the total OVERLAP for each transaction, and divide that into the HOURS_WORKED value to determine the MULTIPLIER_2 value:
SELECT TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, CLOCK_IN, CLOCK_OUT, HOURS_WORKED, ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3) MULTIPLIER_2 FROM (SELECT L1.TRANSACTION_ID, L1.EMPLOYEE_ID, L1.SHIFT_DATE, TO_CHAR(L1.CLOCK_IN,'HH24:MI') CLOCK_IN, TO_CHAR(L1.CLOCK_OUT,'HH24:MI') CLOCK_OUT, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400) AND L1.EMPLOYEE_ID='GREG') GROUP BY TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, CLOCK_IN, CLOCK_OUT, HOURS_WORKED ORDER BY SHIFT_DATE, CLOCK_IN; TRANSACTION_ID EMPLOYEE_ID SHIFT_DAT CLOCK CLOCK HOURS_WORKED MULTIPLIER_2 -------------- ----------- --------- ----- ----- ------------ ------------ 64 GREG 13-FEB-11 00:57 04:56 3.98 0.460 63 GREG 13-FEB-11 02:12 04:45 2.56 0.363 61 GREG 13-FEB-11 03:05 06:53 3.80 0.384 66 GREG 13-FEB-11 03:08 03:15 0.11 0.250 70 GREG 13-FEB-11 03:58 04:08 0.16 0.250 62 GREG 13-FEB-11 05:24 07:09 1.76 0.406 69 GREG 13-FEB-11 06:04 09:22 3.29 0.493 67 GREG 13-FEB-11 07:41 09:07 1.43 0.500 65 GREG 13-FEB-11 09:17 10:24 1.11 0.941 68 GREG 13-FEB-11 10:27 14:03 3.61 1.000 161 GREG 14-FEB-11 01:15 02:24 1.14 0.409 169 GREG 14-FEB-11 01:16 03:45 2.49 0.453 166 GREG 14-FEB-11 01:53 03:54 2.02 0.445 163 GREG 14-FEB-11 03:47 06:55 3.14 0.478 170 GREG 14-FEB-11 05:00 08:16 3.26 0.320 165 GREG 14-FEB-11 06:23 07:56 1.55 0.238 162 GREG 14-FEB-11 06:26 09:54 3.47 0.333 168 GREG 14-FEB-11 06:33 08:10 1.62 0.245 167 GREG 14-FEB-11 08:25 12:20 3.93 0.608 164 GREG 14-FEB-11 11:18 13:25 2.11 0.670
Now let’s try setting the MULTIPLIER_2 values for GREG’s transactions to verify that our SQL statement works:
UPDATE T1 L3 SET MULTIPLIER_2=( SELECT ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3) MULTIPLIER_2 FROM (SELECT L1.TRANSACTION_ID, L1.SHIFT_DATE, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400) AND L1.EMPLOYEE_ID='GREG') L1 WHERE L3.TRANSACTION_ID=L1.TRANSACTION_ID AND L3.SHIFT_DATE=L1.SHIFT_DATE GROUP BY HOURS_WORKED) WHERE L3.EMPLOYEE_ID='GREG'; 20 rows updated.
20 rows updated, that is a good start. Let’s take a look at the rows:
SELECT TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, TO_CHAR(CLOCK_IN,'HH24:MI:SS') CLOCK_IN, TO_CHAR(CLOCK_OUT,'HH24:MI:SS') CLOCK_OUT, HOURS_WORKED, MULTIPLIER_2 FROM T1 WHERE EMPLOYEE_ID='GREG' ORDER BY SHIFT_DATE, CLOCK_IN; TRANSACTION_ID EMPLOYEE_ID SHIFT_DAT CLOCK_IN CLOCK_OU HOURS_WORKED MULTIPLIER_2 -------------- ----------- --------- -------- -------- ------------ ------------ 64 GREG 13-FEB-11 00:57:31 04:56:33 3.98 0.460 63 GREG 13-FEB-11 02:12:04 04:45:36 2.56 0.363 61 GREG 13-FEB-11 03:05:34 06:53:24 3.80 0.384 66 GREG 13-FEB-11 03:08:21 03:15:04 0.11 0.250 70 GREG 13-FEB-11 03:58:45 04:08:28 0.16 0.250 62 GREG 13-FEB-11 05:24:03 07:09:41 1.76 0.406 69 GREG 13-FEB-11 06:04:48 09:22:00 3.29 0.493 67 GREG 13-FEB-11 07:41:20 09:07:06 1.43 0.500 65 GREG 13-FEB-11 09:17:35 10:24:15 1.11 0.941 68 GREG 13-FEB-11 10:27:03 14:03:30 3.61 1.000 161 GREG 14-FEB-11 01:15:40 02:24:01 1.14 0.409 169 GREG 14-FEB-11 01:16:01 03:45:38 2.49 0.453 166 GREG 14-FEB-11 01:53:02 03:54:09 2.02 0.445 163 GREG 14-FEB-11 03:47:15 06:55:34 3.14 0.478 170 GREG 14-FEB-11 05:00:19 08:16:00 3.26 0.320 165 GREG 14-FEB-11 06:23:45 07:56:40 1.55 0.238 162 GREG 14-FEB-11 06:26:06 09:54:15 3.47 0.333 168 GREG 14-FEB-11 06:33:39 08:10:56 1.62 0.245 167 GREG 14-FEB-11 08:25:00 12:20:55 3.93 0.608 164 GREG 14-FEB-11 11:18:45 13:25:08 2.11 0.670
The above is the expected result, let’s generalize the SQL statement to update the rows for the other EMPLOYEE_ID values:
UPDATE T1 L3 SET MULTIPLIER_2=( SELECT ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3) MULTIPLIER_2 FROM (SELECT L1.TRANSACTION_ID, L1.SHIFT_DATE, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400)) L1 WHERE L3.TRANSACTION_ID=L1.TRANSACTION_ID AND L3.SHIFT_DATE=L1.SHIFT_DATE GROUP BY HOURS_WORKED); 140 rows updated.
You could of course verify that the rows were updated correctly, but I will skip that step for now.
COMMIT;
Now let’s go back and modify one of GREG’s transactions, which in my test data happens to be the first transaction for GREG when those transactions are sorted by the CLOCK_IN time:
UPDATE T1 SET CLOCK_OUT=CLOCK_OUT+1/24, HOURS_WORKED=HOURS_WORKED+1 WHERE TRANSACTION_ID=64;
How has that one small change affected the other transactions:
SELECT TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, CLOCK_IN, CLOCK_OUT, HOURS_WORKED, ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3) MULTIPLIER_2, OLD_MULT_2 FROM (SELECT L1.TRANSACTION_ID, L1.EMPLOYEE_ID, L1.SHIFT_DATE, L1.MULTIPLIER_2 OLD_MULT_2, TO_CHAR(L1.CLOCK_IN,'HH24:MI') CLOCK_IN, TO_CHAR(L1.CLOCK_OUT,'HH24:MI') CLOCK_OUT, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400) AND L1.EMPLOYEE_ID='GREG') GROUP BY TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, CLOCK_IN, CLOCK_OUT, HOURS_WORKED, OLD_MULT_2 ORDER BY SHIFT_DATE, CLOCK_IN; TRANSACTION_ID EMPLOYEE_ID SHIFT_DAT CLOCK CLOCK HOURS_WORKED MULTIPLIER_2 OLD_MULT_2 -------------- ----------- --------- ----- ----- ------------ ------------ ---------- 64 GREG 13-FEB-11 00:57 05:56 4.98 0.355 0.460 63 GREG 13-FEB-11 02:12 04:45 2.56 0.363 0.363 61 GREG 13-FEB-11 03:05 06:53 3.80 0.349 0.384 66 GREG 13-FEB-11 03:08 03:15 0.11 0.250 0.250 70 GREG 13-FEB-11 03:58 04:08 0.16 0.250 0.250 62 GREG 13-FEB-11 05:24 07:09 1.76 0.361 0.406 69 GREG 13-FEB-11 06:04 09:22 3.29 0.493 0.493 67 GREG 13-FEB-11 07:41 09:07 1.43 0.500 0.500 65 GREG 13-FEB-11 09:17 10:24 1.11 0.941 0.941 68 GREG 13-FEB-11 10:27 14:03 3.61 1.000 1.000 161 GREG 14-FEB-11 01:15 02:24 1.14 0.409 0.409 169 GREG 14-FEB-11 01:16 03:45 2.49 0.453 0.453 166 GREG 14-FEB-11 01:53 03:54 2.02 0.445 0.445 163 GREG 14-FEB-11 03:47 06:55 3.14 0.478 0.478 170 GREG 14-FEB-11 05:00 08:16 3.26 0.320 0.320 165 GREG 14-FEB-11 06:23 07:56 1.55 0.238 0.238 162 GREG 14-FEB-11 06:26 09:54 3.47 0.333 0.333 168 GREG 14-FEB-11 06:33 08:10 1.62 0.245 0.245 167 GREG 14-FEB-11 08:25 12:20 3.93 0.608 0.608 164 GREG 14-FEB-11 11:18 13:25 2.11 0.670 0.670
The above shows that we now need to readjust the MULTIPLIER_2 value for three transactions because we set the CLOCK_OUT time of the first transaction to be an hour later. Let’s just retrieve the rows that need to be adjusted:
SELECT TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, CLOCK_IN, CLOCK_OUT, HOURS_WORKED, ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3) MULTIPLIER_2, OLD_MULT_2 FROM (SELECT L1.TRANSACTION_ID, L1.EMPLOYEE_ID, L1.SHIFT_DATE, L1.MULTIPLIER_2 OLD_MULT_2, TO_CHAR(L1.CLOCK_IN,'HH24:MI') CLOCK_IN, TO_CHAR(L1.CLOCK_OUT,'HH24:MI') CLOCK_OUT, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400) AND L1.EMPLOYEE_ID='GREG') GROUP BY TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, CLOCK_IN, CLOCK_OUT, HOURS_WORKED, OLD_MULT_2 HAVING ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3)<>OLD_MULT_2 ORDER BY SHIFT_DATE, CLOCK_IN; TRANSACTION_ID EMPLOYEE_ID SHIFT_DAT CLOCK CLOCK HOURS_WORKED MULTIPLIER_2 OLD_MULT_2 -------------- ----------- --------- ----- ----- ------------ ------------ ---------- 64 GREG 13-FEB-11 00:57 05:56 4.98 0.445 0.460 61 GREG 13-FEB-11 03:05 06:53 3.80 0.349 0.384 62 GREG 13-FEB-11 05:24 07:09 1.76 0.361 0.406
Finally, let’s update just those rows where the MULTIPLIER_2 value should be adjusted using a modified version of the above SQL statement (there might be an easier way to perform this update that also does not resort to procedural type code):
UPDATE T1 L3 SET MULTIPLIER_2 = ( SELECT ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3) MULTIPLIER_2 FROM (SELECT L1.TRANSACTION_ID, L1.MULTIPLIER_2 OLD_MULT_2, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400) AND L1.EMPLOYEE_ID='GREG') L1 WHERE L3.TRANSACTION_ID=L1.TRANSACTION_ID GROUP BY TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, HOURS_WORKED, OLD_MULT_2 HAVING ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3)<>OLD_MULT_2) WHERE L3.TRANSACTION_ID IN ( SELECT TRANSACTION_ID FROM (SELECT L1.TRANSACTION_ID, L1.MULTIPLIER_2 OLD_MULT_2, ROUND((DECODE(SIGN(L1.CLOCK_OUT-L2.CLOCK_OUT),-1,L1.CLOCK_OUT,L2.CLOCK_OUT) - DECODE(SIGN(L1.CLOCK_IN-L2.CLOCK_IN),-1,L2.CLOCK_IN,L1.CLOCK_IN)) * 24,2) OVERLAP, L1.HOURS_WORKED FROM T1 L1, T1 L2 WHERE L1.EMPLOYEE_ID=L2.EMPLOYEE_ID AND L1.SHIFT_DATE=L2.SHIFT_DATE AND (L1.CLOCK_IN BETWEEN L2.CLOCK_IN AND L2.CLOCK_OUT-1/86400 OR L2.CLOCK_IN BETWEEN L1.CLOCK_IN AND L1.CLOCK_OUT-1/86400) AND L1.EMPLOYEE_ID='GREG') L1 WHERE L3.TRANSACTION_ID=L1.TRANSACTION_ID GROUP BY TRANSACTION_ID, EMPLOYEE_ID, SHIFT_DATE, HOURS_WORKED, OLD_MULT_2 HAVING ROUND(HOURS_WORKED/DECODE(SUM(OVERLAP),0,1,SUM(OVERLAP)),3)<>OLD_MULT_2); 3 rows updated.
In this ERP package there is a second way that the time for concurrent machine transactions (labor tickets) may be split, and this method is typically used when a single shop resource is used, such as a paint booth, to work on multiple jobs concurrently. In such a case, with four concurrent operations, overstating the machine burden rate by a factor of 4 is an unwise decision. To handle this situation, the ERP package’s modules will take the calculated MULTIPLIER_2 value, multiply that by the HOURS_WORKED (the machine burden hours), set that value as the new HOURS_WORKED value, and then set the MULTIPLIER_2 to a value of 1.0.
Not so confusing… and probably not to useful to most of the readers of this blog. However, the above process of attacking a problem might be helpful for other types of problems.
Recent Comments