nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Adolfo Casari

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.

All comments

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.

Adolfo Casari

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_ebyoek

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira