I am working on a report that will calculate the MTTR (mean time to repair) for all tickets while they are assigned to a specific group (department). What I need to do now is select the appropriate date/time from 1 or 2 specific records for each ticket #, but that's where am lost. Only when the ticket is in certain status' will the time count against the MTTR.
Example:
Ticket # Status Enter Date/Time
4111 Pending 12/15/2001 9:45:21 PM
4111 Resolved 12/19/2001 11:50:31 AM
4111 Closed 12/19/2001 11:51:48 AM
4310 New 12/27/2001 8:49:59 PM
4310 Pending 12/28/2001 4:03:17 PM
4310 Pending 12/28/2001 4:31:07 PM
4310 Resolved 12/28/2001 4:32:14 PM
4310 Closed 12/28/2001 4:32:20 PM
4402 New 1/3/2002 3:32:54 PM
4402 New 1/3/2002 3:34:27 PM
If the ticket is in "Pending", "Resolved", or "Closed" status the time does not count against the MTTR. So I need to select the date/time for the first status that counts against the MTTR and the first status that does not count aginst the MTTR and calc the difference. In the example, ticket 4111 has an MTTR time of 0, ticket 4310 has an MTTR of 19.23, and ticket 4402 has an MTTR of 0.03. I used the following formula to calc the times.
For ticket 4111:
DateDiff ("n",#12/15/2001 9:45:21 PM#,#12/15/2001 9:45:21 PM# ) / 60
For ticket 4310:
DateDiff ("n",#12/27/2001 8:49:59 PM#,#12/28/2001 4:03:17 PM# ) / 60
For ticket 4402:
DateDiff ("n",#1/3/2002 3:32:54 PM#,#1/3/2002 3:34:27 PM# ) / 60
The reason I divided by 60 is because it gives a more accurate number of hours, than the "h" interval type value. I also wanted to everyone know that there are numerical values associated with each status. I wrote a formula to change the number to the appropriate status.
Example:
If {STATUS} = 0
then 'New'
else if {STATUS} = 1
then 'Assigned'
else if {STATUS} = 2
then 'Request Reassign'
else if {STATUS} = 3
then 'Acknowledged'
else if {STATUS} = 4
then 'WIP'
else if {STATUS} = 5
then 'Pending'
else if {STATUS} = 6
then 'Resolved'
else if {STATUS} = 7
then 'Closed'
Is it even possible to write a formula that will select the appropriate date/times, or am I just wishful thinking?
Example:
Ticket # Status Enter Date/Time
4111 Pending 12/15/2001 9:45:21 PM
4111 Resolved 12/19/2001 11:50:31 AM
4111 Closed 12/19/2001 11:51:48 AM
4310 New 12/27/2001 8:49:59 PM
4310 Pending 12/28/2001 4:03:17 PM
4310 Pending 12/28/2001 4:31:07 PM
4310 Resolved 12/28/2001 4:32:14 PM
4310 Closed 12/28/2001 4:32:20 PM
4402 New 1/3/2002 3:32:54 PM
4402 New 1/3/2002 3:34:27 PM
If the ticket is in "Pending", "Resolved", or "Closed" status the time does not count against the MTTR. So I need to select the date/time for the first status that counts against the MTTR and the first status that does not count aginst the MTTR and calc the difference. In the example, ticket 4111 has an MTTR time of 0, ticket 4310 has an MTTR of 19.23, and ticket 4402 has an MTTR of 0.03. I used the following formula to calc the times.
For ticket 4111:
DateDiff ("n",#12/15/2001 9:45:21 PM#,#12/15/2001 9:45:21 PM# ) / 60
For ticket 4310:
DateDiff ("n",#12/27/2001 8:49:59 PM#,#12/28/2001 4:03:17 PM# ) / 60
For ticket 4402:
DateDiff ("n",#1/3/2002 3:32:54 PM#,#1/3/2002 3:34:27 PM# ) / 60
The reason I divided by 60 is because it gives a more accurate number of hours, than the "h" interval type value. I also wanted to everyone know that there are numerical values associated with each status. I wrote a formula to change the number to the appropriate status.
Example:
If {STATUS} = 0
then 'New'
else if {STATUS} = 1
then 'Assigned'
else if {STATUS} = 2
then 'Request Reassign'
else if {STATUS} = 3
then 'Acknowledged'
else if {STATUS} = 4
then 'WIP'
else if {STATUS} = 5
then 'Pending'
else if {STATUS} = 6
then 'Resolved'
else if {STATUS} = 7
then 'Closed'
Is it even possible to write a formula that will select the appropriate date/times, or am I just wishful thinking?