## Calculating Overlapping Hours for Labor Transactions

15 02 2011

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.