Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following data in notepad file for each application. Application_Id is unique id.
Process_Code Application_id Workitem_Id Activity_Code Txn_Date State_Code Assigned_User_Id Assigned_Dept_Id Actioned_User_Id
NAP 10003507 79909714 NBG 2013-09-06 10:32:39.900 CMP NULL NULL 99999
NAP 10003507 79909714 NBG 2013-09-06 10:32:39.900 RDY NULL NULL 99999
NAP 10003507 79909715 NSA 2013-09-06 10:32:39.900 RDY NULL NULL 99999
NAP 10003507 79909715 NSA 2013-09-11 09:00:28.380 RUN NULL NULL 12781
NAP 10003507 79909715 NSA 2013-09-11 09:21:09.713 CMP NULL NULL 12781
NAP 10003507 80307667 NSX 2013-09-11 09:21:09.713 RDY NULL NULL 12781
NAP 10003507 80307667 NSX 2013-09-11 09:44:07.147 RUN NULL NULL 29037
NAP 10003507 80307667 NSX 2013-09-11 09:44:10.617 CMP NULL NULL 29037
NAP 10003507 80308416 NSA 2013-09-11 09:44:10.617 RDY NULL NULL 29037
NAP 10003507 80308416 NSA 2013-09-11 09:44:11.787 RUN NULL NULL 29037
NAP 10003507 80308416 NSA 2013-09-11 09:44:29.117 CMP NULL NULL 29037
NAP 10003507 80308424 NSX 2013-09-11 09:44:29.117 RDY NULL NULL 29037
NAP 10003507 80308424 NSX 2013-09-11 09:44:34.600 TRM NULL NULL 29037
NAP 10003507 80403761 NBG 2013-09-17 17:01:54.310 RDY NULL NULL 99999
NAP 10003507 80403761 NBG 2013-09-17 17:01:54.327 CMP NULL NULL 99999
NAP 10003507 80403762 NSA 2013-09-17 17:01:54.327 RDY NULL NULL 99999
NAP 10003507 80403762 NSA 2013-09-19 15:48:37.117 RUN NULL NULL 29037
NAP 10003507 80403762 NSA 2013-09-19 15:57:16.813 CMP NULL NULL 29037
NAP 10003507 80438748 CJA 2013-09-19 15:57:16.813 RDY NULL NULL 29037
NAP 10003507 80438748 CJA 2013-09-19 16:02:13.400 CMP NULL NULL 99999
NAP 10003507 80438829 NCO 2013-09-19 16:02:13.413 RDY NULL NULL 99999
NAP 10003507 80438829 NCO 2013-09-19 16:20:41.390 RUN NULL NULL 29396
NAP 10003507 80438829 NCO 2013-09-19 16:21:07.717 CMP NULL NULL 29396
NAP 10003507 80439054 NFU 2013-09-19 16:21:07.717 SUS NULL NULL 29396
NAP 10003507 80439054 NFU 2013-09-25 08:21:40.923 RDY NULL NULL 88603
NAP 10003507 80439054 NFU 2013-09-25 08:21:41.033 RUN NULL NULL 88603
NAP 10003507 80439054 NFU 2013-09-25 08:21:50.877 CMP NULL NULL 88603
NAP 10003507 80507822 NCH 2013-09-25 08:21:50.893 RDY NULL NULL 88603
NAP 10003507 80507822 NCH 2013-09-25 15:24:55.157 RUN NULL NULL 15095
NAP 10003507 80507822 NCH 2013-09-25 15:24:58.657 RDY NULL NULL 15095
NAP 10003507 80507822 NCH 2013-09-25 15:25:05.563 RUN NULL NULL 15095
NAP 10003507 80507822 NCH 2013-09-25 15:28:54.660 CMP NULL NULL 15095
NAP 10003507 80515611 NBO 2013-09-25 15:28:54.660 RDY NULL NULL 15095
NAP 10003507 80515611 NBO 2013-09-25 15:28:58.067 RUN NULL NULL 15095
NAP 10003507 80515611 NBO 2013-09-25 15:29:02.910 CMP NULL NULL 15095
NAP 10003507 80515614 NCA 2013-09-25 15:29:02.910 CMP NULL NULL 99999
NAP 10003507 80515614 NCA 2013-09-25 15:29:02.910 RDY NULL NULL
Each application passes through different trays but I am only intersted in the following Activity_Code of trays.
Tray | Activity_Code | Application Status |
NAPS Contact Customer | NCO | Outbound |
NAPS Print Documents | NDO | Outbound |
NAPS Follow Up Docs | NFU | Customer |
NAPS Returned Documents | NRD | Customer |
NAPS Check Documents | NCH | Inbound |
NAPS Security Perfection | NSP | Security |
NAPS Sec Special Conditions | NSC | Booked |
NAPS Booking | NBO | Booked |
NAPS Call Off | NCA | Booked |
Each tray has different State_Code a below.
State_Code | Code description |
RDY | Ready to work |
RUN | In process |
TRM | Terminated |
CMP | Completed |
I want to calculate time in hours only workingdays for each application_status if State_code is 'CMP' and application changes its application_status from one to other e.g. application moves from Outbound to Customer trays when State_code is CMP. Workinh hours are start time = 9:00:00 and quit time = 17:00:00.