JIRA SQL: find average time in status X in

[Adolfo Casari]

Hi,

I am trying to build a sql query (postgres) that will return the average time issues spent in status X given a timefrime (for instance in current month).

Has anyone tried this?

Thanks in advance.

[Adolfo Casari · Accepted solution]

I found was I was looking for here:

https://answers.atlassian.com/questions/66047

After some minor changes for POSTGRES it shows the same info you get in the Transition tabs for the issue, i.e., Time in Status. Then I had to average it over all issues for each state.

[Robert Oliveira]

Hi, can you share the settings you made to work in postgree? Thank you very much in advance.

[Jarrod Moura]

For those looking for the answer in SQL, here it is:

WITH statusHistory AS
(select
jiraissue.ID, changeitem.OLDSTRING OldStatus, changeitem.NEWSTRING NewStatus, changegroup.CREATED Executed,
DATEDIFF(minute, changegroup.CREATED, Lead(changegroup.CREATED) OVER (Partition By jiraissue.ID ORDER BY changegroup.CREATED)) MinutesInStatus,
ROW_NUMBER() OVER (Partition By jiraissue.ID order by changegroup.CREATED) StatusOrder
from changeitem (nolock)
inner join changegroup (nolock) on changeitem.groupid = changegroup.id
inner join jiraissue (nolock) on jiraissue.id = changegroup.issueid
where
changeitem.field ='status'
)

select ID, OldStatus, NewStatus, Executed, ISNULL(MinutesInStatus, DATEDIFF(minute, Executed, GETDATE())) MinutesInStatus, StatusOrder
from statusHistory

[deepali3031]

For those looking for postgres query, This will give you Time in status 

WITH statusHistory AS
(select
jiraissue.ID, p.pname, p.pkey, jiraissue.issuenum, jiraissue.priority, pri.pname as priorityname, c.cname as componentname, pv.id as fixversionId, pv.vname as fixversion, jiraissue.created as issueCreated, jiraissue.resolutiondate, changeitem.OLDSTRING OldStatus, changeitem.NEWSTRING NewStatus, changegroup.CREATED Executed,
changegroup.CREATED - lag(changegroup.CREATED) over (PARTITION BY jiraissue.ID order by changegroup.CREATED) as MinutesInStatus,
ROW_NUMBER() OVER (Partition By jiraissue.ID order by changegroup.CREATED) StatusOrder
from changeitem
inner join changegroup on changeitem.groupid = changegroup.id
inner join jiraissue on jiraissue.id = changegroup.issueid
inner join project p on jiraissue.project = p.id
inner join nodeassociation na on na.SOURCE_NODE_ID = jiraissue.ID
inner join projectversion pv on pv.id = na.SINK_NODE_ID
join priority pri on pri.id = jiraissue.priority
inner join component c on na.sink_node_id = c.id
where
changeitem.field ='status' AND changeitem.FIELDTYPE='jira' And pkey = 'HM'
)

Select * From statusHistory

[Zubaidullou]

Hi Adolfo,

I have tried this logic, but not from DB, I tried getting data from JIRA server by JIRA REST Java Client on JIRA has jql you can use it.