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.








Follow

Get every new post delivered to your Inbox.

Join 143 other followers