Urgent - Exclude Timestamp differece Zeros and -

Mon, 03 Mar 2014 14:54:00 GMT

The application can move in different trays in the workflow. Each tray has different State_Codes. e.g. Tray = NSS has State_code RDY, RUN & CMP in the following example. But each tray would have RDY (shows application came in a tray) and CMP (application completed in a tray). How can I get that how many working hours each application stayed in each tray. Working days are Monday to Friday and working hours are 9:00-17:00. There is also a problem that the application can go in each tray once or more than once as two examples below.

Once example: In this example application moved in each tray only once.

Application_id	Trays	Txn_Date	State_Code
10007344	NBG	18/09/2013 12:33	CMP
10007344	NBG	18/09/2013 12:33	RDY
10007344	NSS	18/09/2013 16:23	RDY
10007344	NSS	19/09/2013 09:05	RUN
10007344	NSS	19/09/2013 09:07	CMP
10007344	CJA	19/09/2013 09:07	RDY
10007344	CJA	19/09/2013 09:11	CMP
10007344	NCO	19/09/2013 09:11	RDY
10007344	NCO	19/09/2013 09:20	RUN
10007344	NCO	19/09/2013 09:20	CMP
10007344	NFU	25/09/2013 08:28	RDY
10007344	NFU	25/09/2013 08:28	RUN
10007344	NFU	25/09/2013 08:28	CMP
10007344	NCH	25/09/2013 08:28	RDY
10007344	NCH	25/09/2013 11:56	RUN
10007344	NCH	25/09/2013 12:02	CMP
10007344	NBO	25/09/2013 12:02	RDY
10007344	NBO	25/09/2013 12:03	RUN
10007344	NBO	25/09/2013 12:07	CMP
10007344	NCA	25/09/2013 12:07	CMP
10007344	NCA	25/09/2013 12:07	RDY

More than Once example: Application moved into NSA twice, CJA three times etc.

Application_id	Tray	Txn_Date	State_Code
10007616	NBG	12/09/2013 10:42:29	CMP
10007616	NBG	12/09/2013 10:42:29	RDY
10007616	NSA	12/09/2013 10:42:29	RDY
10007616	NSA	12/09/2013 11:25:55	RUN
10007616	NSA	12/09/2013 11:26:33	CMP
10007616	CJA	12/09/2013 11:26:33	RDY
10007616	CJA	12/09/2013 11:27:24	CMP
10007616	NCO	12/09/2013 11:27:24	RDY
10007616	NCO	12/09/2013 12:56:32	RUN
10007616	NCO	12/09/2013 13:00:24	CMP
10007616	NSA	12/09/2013 13:00:24	RDY
10007616	NSA	13/09/2013 16:20:38	RUN
10007616	NSA	13/09/2013 16:22:08	CMP
10007616	CJA	13/09/2013 16:22:08	RDY
10007616	CJA	13/09/2013 16:25:47	CMP
10007616	NCO	13/09/2013 16:25:47	RDY
10007616	NCO	13/09/2013 16:50:57	RUN
10007616	NCO	13/09/2013 16:51:47	RDY
10007616	NCO	16/09/2013 10:42:22	RUN
10007616	NCO	16/09/2013 10:43:00	CMP
10007616	NFU	16/09/2013 10:43:00	SUS
10007616	NFU	24/09/2013 08:31:02	RDY
10007616	NFU	24/09/2013 08:31:02	RUN
10007616	NFU	24/09/2013 08:31:05	CMP
10007616	NCH	24/09/2013 08:31:05	RDY
10007616	NCH	24/09/2013 12:13:58	RUN
10007616	NCH	24/09/2013 12:15:25	CMP
10007616	NBO	24/09/2013 12:15:25	RDY
10007616	NBO	24/09/2013 12:15:26	RUN
10007616	NBO	24/09/2013 12:16:01	RDY
10007616	NBO	24/09/2013 12:16:04	RUN
10007616	NBO	24/09/2013 12:16:05	EXP
10007616	NCH	24/09/2013 12:16:05	RDY
10007616	NCH	24/09/2013 13:25:26	RUN
10007616	NCH	24/09/2013 13:26:20	CMP
10007616	NRD	24/09/2013 13:26:20	RDY
10007616	NRD	24/09/2013 15:30:05	RUN
10007616	NRD	24/09/2013 15:30:07	EXP
10007616	NCH	24/09/2013 15:30:07	RDY
10007616	NCH	24/09/2013 15:30:08	RUN
10007616	NCH	24/09/2013 15:31:42	CMP
10007616	NBO	24/09/2013 15:31:42	RDY
10007616	NBO	24/09/2013 15:31:43	RUN
10007616	NBO	24/09/2013 15:37:25	RDY
10007616	NBO	24/09/2013 15:37:27	RUN
10007616	NBO	24/09/2013 15:37:32	EXP
10007616	NSA	24/09/2013 15:37:32	RDY
10007616	NSA	24/09/2013 15:39:51	RUN
10007616	NSA	24/09/2013 15:41:37	CMP
10007616	CJA	24/09/2013 15:41:37	RDY
10007616	CJA	24/09/2013 15:43:42	CMP
10007616	NCO	24/09/2013 15:43:42	RDY
10007616	NCO	24/09/2013 15:52:46	RUN
10007616	NCO	24/09/2013 15:53:44	CMP
10007616	NFU	24/09/2013 15:53:44	SUS
10007616	NFU	24/09/2013 15:53:47	RDY
10007616	NFU	24/09/2013 15:53:47	RUN
10007616	NFU	24/09/2013 15:54:06	CMP
10007616	NCH	24/09/2013 15:54:06	RDY
10007616	NCH	24/09/2013 15:54:08	RUN
10007616	NCH	24/09/2013 15:54:12	CMP
10007616	NBO	24/09/2013 15:54:12	RDY
10007616	NBO	24/09/2013 15:54:14	RUN
10007616	NBO	24/09/2013 15:55:56	CMP
10007616	NCA	24/09/2013 15:55:56	CMP
10007616	NCA	24/09/2013 15:55:56	RDY

topic Urgent - Exclude Timestamp differece Zeros and - in QlikView

Urgent - Exclude Timestamp differece Zeros and -