I don't understand completely the logic how the data are stored but I think you will need to check within a sorted load the previous records and create an additional field "End date" and a flag-field to differ the resulted usage- and garbage-records (deleting them within a further load). This could be achived with Peek() or Previous() ?
there are two tables one incident (contains: incident number, open, close, assigment group, assigned to, source ... etc.)
But there is only one record per incident. To see details what happen between open and close i have to use metric instance table where are many metric definition like assigment group, assigned to, status etc.
Unfortunately all metric definition are stored in the same table (metric_instance) so first thing I have preload metric_instance table with definition (assigment group and assigned to only)
There is a "value" field which contains assigment group name in case for metric definition Assigment group, or person for metric definition Assigned To.
Metric instance is connected with incident by field: incident number
This is really difficult task so i will try once again explain what is a problem.
There is one fact table called "incident" incident contains information about
- inc number, start date, end date, assignment group, assigned to, source, country, customer, ... and more
But one Incident can be handled by many assigment groups and by many people. Incidents table have always the latest group and person who worked on the case. (updating only one record per incident)
There is another table called "metric_instance" which contains fields like:
- Metric definition: the metric definition for which this metric instance was gathered.
- Value : For a “Field value duration” metric this is the value of the table field for which duration is calculated. For example, for the “Assigned to Duration” metric, the Value is the name of the person assigned to the incident. For other metrics, the value can be any value calculated by the metric.
- ID : Identifies the specific record for which the metric is gathered. For example, the specific incident.
- Duration : Time duration for a Field value duration metric.
Duration is only measure the time between metric start and metric end
I need calculate difference between metric start from the row: M_ID: 2 and metric start from the row: M_ID: 1
So if we analyze handling process of an incident in theory it should follow the steps:
M_ID Value metric start metric end definiton Incident 1 Service Desk 19/11/2015 13:20:40 30/11/2015 11:24:32 Assigment group inc00001 2 USER 001 26/11/2015 16:32:42 30/11/2015 11:24:32 Assigned to inc00001 3 Service Desk tier 2 30/11/2015 11:24:32 10/12/2015 11:30:54 Assigment group inc00001 4 USER 002 30/11/2015 11:28:32 10/12/2015 11:30:54 Assigned to inc00001 5 Service Desk tier 3 10/12/2015 11:30:54 05/01/2016 11:09:06 Assigment group inc00001 6 USER 003 05/01/2016 09:11:12 05/01/2016 11:09:06 Assigned to inc00001 Case solved
So incident was assigned to Service Desk group, than USER 001 from Service Desk start work on the case
USER 001 decided escalate the case to the tier 2, in Tier 2 User from this group after some time decided also escalate case to the higher tier 3. USER 003 from the Tier 3 solved and closed the case.
Each action is stored in separate record.
Another problem is the order is not always like in this example sometimes case is handled like we called ping-pong case
M_ID Value metric start metric end definiton Incident 1 Service Desk 19/11/2015 13:20:40 30/11/2015 11:24:32 Assigment group inc00001 2 USER 001 26/11/2015 16:32:42 30/11/2015 11:24:32 Assigned to inc00001 3 USER 002 30/11/2015 11:24:32 10/12/2015 11:30:54 Assigned to inc00001 4 Service Desk tier 2 30/11/2015 11:24:32 10/12/2015 11:30:54 Assigment group inc00001 5 Escalation group 10/12/2015 11:30:54 05/01/2016 09:11:12 Assigment group inc00001 6 Service Desk tier 3 05/01/2016 09:11:12 05/01/2016 11:09:06 Assigment group inc00001 7 Service Desk tier 2 05/01/2016 11:09:06 08/01/2016 14:10:04 Assigment group inc00001 8 USER 002 05/01/2016 11:35:38 08/01/2016 14:10:04 Assigned to inc00001 9 Service Desk tier 3 08/01/2016 14:10:04 11/01/2016 09:42:39 Assigment group inc00001 10 Service Desk tier 2 11/01/2016 09:42:39 11/01/2016 10:06:49 Assigment group inc00001 11 USER 002 11/01/2016 09:52:33 11/01/2016 10:06:49 Assigned to inc00001 12 Service Desk tier 3 11/01/2016 10:06:49 11/01/2016 11:31:15 Assigment group inc00001 13 Service Desk 11/01/2016 11:31:15 27/01/2016 18:00:14 Assigment group inc00001 14 USER 001 11/01/2016 14:50:12 27/01/2016 18:00:14 Assigned to inc00001 Case solved
The goal is start to measure response time per each Assignment group. So it is need in order to benchmark Team performance
I think if you sorted your data to the incident-id and the metric start and used then a within an if-loop a peek/previous - see link above - function to access the previous record you could calculate your response-times which would look like:
if(Incident = previous(Incident); [metric start] - previous([metric end]) as ResponseTime
Ok so as you adviced
RowNo() as M_AGS.RowNumber,
M_AGS.start as M_AGS.start,
if(Previous(M_AGS.flag)=1 and Previous(M_AGS.flag)<>M_AGS.flag, M_AGS.start -Previous(M_AGS.start) ,0) as M_AGS.dispatch_time,
M_AGS.flag, // 1 for Assigment group metric definition, 0 for assigned to metric definition
M_AGS.end as M_AGS.end
Resident M_AGS order by task.sys_id,M_AGS.start;
the result is:
Metric definition Value M_AGS.start dispatch_time Assignment Group (incident) Service Desk 19/11/2015 13:20:40 0 Assigned to Duration USER 001 26/11/2015 16:32:42 7.1333 Assignment Group (incident) Service Desk tier 2 30/11/2015 11:24:32 0 Assigned to Duration Escalation group 30/11/2015 11:24:32 0 Assignment Group (incident) Escalation group 10/12/2015 11:30:54 0 Assignment Group (incident) Service Desk tier 3 05/01/2016 09:11:12 0 Assignment Group (incident) Service Desk tier 2 05/01/2016 11:09:06 0 Assigned to Duration USER 002 05/01/2016 11:35:38 0.01842 Assignment Group (incident) Service Desk tier 3 08/01/2016 14:10:04 0 Assignment Group (incident) Service Desk tier 2 11/01/2016 09:42:39 0 Assigned to Duration USER 002 11/01/2016 09:52:33 0.00687 Assignment Group (incident) Service Desk tier 3 11/01/2016 10:06:49 0 Assignment Group (incident) Service Desk 11/01/2016 11:31:15 0 Assigned to Duration USER 001 11/01/2016 14:50:12 0.13815
So this is exactly solution of my problem
Now I need add big loop for all tasks