Has anyone done a mean time to recovery report from JIRA (server)?

[Phebe Brown]

Just had a request for the following reporting:

• Mean Time to Recovery: The average time it takes for a ticket to move from Reopened back to Production during a sprint.

SO I'd need all the tickets that were reopened in a given sprint, then how long it took to go from re-opened to resolved.

[Phebe Brown]

Here's what I have so far for SQL : I still have to  make it sprint specific:

select ji1.id, ji1.issuenum, cg1.created reopened_date, cg2.created closed_date, DATEDIFF(DATE(cg2.created),DATE(cg1.created)) days_reopen

from jiraissue ji1, changegroup cg1, changeitem ci1, jiraissue ji2, changegroup cg2, changeitem ci2

where ji1.id = cg1.issueid

and cg1.id = ci1.groupid

and ji1.project = <project ID>

and ci1.newvalue like '4' 

and ci1.newstring like "Reopened" 

and ci1.FIELD = 'status'

and ji2.id = cg2.issueid

and cg2.id = ci2.groupid

and ji2.project = <project ID>

and ci2.newvalue in ('5','6') 

and ci2.FIELD = 'status'

and ji1.id = ji2.id

and cg2.created =

(select max(`CREATED`) from changegroup aa, changeitem bb

where aa.issueid =cg2.issueid

and bb.FIELD = 'status'

and aa.id = bb.groupid

)