Getting transactional data from excel for reporting
I've been asked to build a report on how long it takes us work on tickets. I have managed to get the data from the database, however I need to anaysis this now and these tickets have 3 options:

Option 1:

If a ticket ID has only got a reference to queue 26 as the old value, then I’m looking for the time it takes for the ticket to be created then moved from the queue. (ID 1000)

Option 2:

If a ticket ID has references to queue 26 as the old value and the new value, then I’m looking for the time difference between the ticket entering queue and leaving it (i.e. one record will show new value as 26 and another record will show old value as 26). Some tickets might have multiple occurrences, I’m after the time for each occurrence. (ID 1003)

Option 3:

If a ticket ID has only got a reference to queue 26 as the new value, then I’m looking for the time it takes for the ticket to be moved into the queue to being resolved. (ID 1004)

I'm thinking that I need to createa VBA script for this. I have tried with pivot tables and also running some count fucntions but have hit lots of brick walls. Could anyone help?


id	|Subject	|Created	         |NewValue	|OldValue	|Value changed	|Resolved

1000	Ticket A	02/04/2012 12:50	34	26	03/04/2012 10:22	01/01/1970 00:00

1001	Ticket B	03/04/2012 09:35	34	26	03/04/2012 10:47	05/04/2012 08:51

1002	Ticket C	03/04/2012 09:43	40	26	03/04/2012 10:08	03/05/2012 15:14

1002	Ticket C	03/04/2012 09:43	26	3	12/04/2012 14:18	03/05/2012 15:14

1003	Ticket D	03/04/2012 10:07	34	26	03/04/2012 12:54	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	26	34	02/05/2012 11:18	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	34	26	03/05/2012 09:32	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	26	34	18/05/2012 11:46	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	34	26	18/05/2012 12:38	18/05/2012 17:57

1004	Ticket E	03/04/2012 11:09	26	3	10/04/2012 07:53	10/04/2012 08:56

1005	Ticket F	04/04/2012 08:45	34	26	04/04/2012 08:58	05/04/2012 09:39

1005	Ticket F	04/04/2012 08:45	26	34	05/04/2012 08:04	05/04/2012 09:39

Getting transactional data from excel for reporting
===

I've been asked to build a report on how long it takes us work on tickets. I have managed to get the data from the database, however I need to anaysis this now and these tickets have 3 options:

Option 1:

If a ticket ID has only got a reference to queue 26 as the old value, then I’m looking for the time it takes for the ticket to be created then moved from the queue. (ID 1000)

Option 2:

If a ticket ID has references to queue 26 as the old value and the new value, then I’m looking for the time difference between the ticket entering queue and leaving it (i.e. one record will show new value as 26 and another record will show old value as 26). Some tickets might have multiple occurrences, I’m after the time for each occurrence. (ID 1003)

Option 3:

If a ticket ID has only got a reference to queue 26 as the new value, then I’m looking for the time it takes for the ticket to be moved into the queue to being resolved. (ID 1004)

I'm thinking that I need to createa VBA script for this. I have tried with pivot tables and also running some count fucntions but have hit lots of brick walls. Could anyone help?


id	|Subject	|Created	         |NewValue	|OldValue	|Value changed	|Resolved

1000	Ticket A	02/04/2012 12:50	34	26	03/04/2012 10:22	01/01/1970 00:00

1001	Ticket B	03/04/2012 09:35	34	26	03/04/2012 10:47	05/04/2012 08:51

1002	Ticket C	03/04/2012 09:43	40	26	03/04/2012 10:08	03/05/2012 15:14

1002	Ticket C	03/04/2012 09:43	26	3	12/04/2012 14:18	03/05/2012 15:14

1003	Ticket D	03/04/2012 10:07	34	26	03/04/2012 12:54	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	26	34	02/05/2012 11:18	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	34	26	03/05/2012 09:32	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	26	34	18/05/2012 11:46	18/05/2012 17:57

1003	Ticket D	03/04/2012 10:07	34	26	18/05/2012 12:38	18/05/2012 17:57

1004	Ticket E	03/04/2012 11:09	26	3	10/04/2012 07:53	10/04/2012 08:56

1005	Ticket F	04/04/2012 08:45	34	26	04/04/2012 08:58	05/04/2012 09:39

1005	Ticket F	04/04/2012 08:45	26	34	05/04/2012 08:04	05/04/2012 09:39
