Sorry for the delay in response.
As I understand it the process goes as follows
We pull out shipment data from our database in one month periods.
SHIPMENTS extract should contain (in this order):
Shipment_ID / Order_ID /Current_Area / Arrive_in_Current_Area / Original_Order_Date
Note that Original_Order_Date is from ORDERS table not SHIPMENTS table.
SHIPMENT HISTORY extract should contain (in this order):
Shipment_ID / Order_ID /Current_Area / Arrive_in_Current_Area / Original_Order_Date /Shipment_ID /Prod_Area
There are 12 possible queues(prod areas) that an order can travel thru they are
FINISH SHIP1 HOLD BALQ1 DCB1 FLD CLAIMS STEX1 SREX1 SRDC1 STECH1 CSETL and CAUTH
Everything after FLD CLAIMS is considered an "exception" queue (This becomes important later)
We process how long the orders remained in each queue by subtracting the date/time the order was created from the date/time the order finished
using data from our shipment history table we then calculate if an order spent any time in an "exception" queue.
This is done in the current excel sheet by an array
We then take this sheet after it caluclates and copy it to a sumamry sheet which gives us an average
Once that average is caluclated it gives us two numbers
Avg turn around time per order ( In july that was 1.1 days)
This is the excel code
TAT No Exceptions
and AVG turn around time with exceptions ( July = 4.9 days)
And TAT ALL
I hope this helps. Ive been wracking my brains trying to figure out how to do this but I cannot figure out the array (as ive never used them before).
If anyone needs any more information ill be more than happy to provide whatever I can.
I appreciate the help. Im getting thrown into the middle of this and I know that this current system (with its 4 hours of processing time) can be made better thru crystal somwhow.
Anything worth doing is worth messing up at least 5 times before you get it right!