I have the following data in notepad file.
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.
| 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 in each application_status when State_code is 'CMP' when application changes its application_status from one to other e.g. from outbound to customer. start time = 9:00:00 and quit time = 17:00:00.
