Get "time in source status" value using SQL

Hi,

Our team uses over 15 custom statuses within a certain workflow. I'd like to measure cycle times by looking at the average of the field "Time in source status" over a large group of issues.

I've read elsewhere that this value is calculated on the fly, so I assume I won't find it in the database.

I have a SQL query that returns the issue key, issuetype, old status, the new status, and the time the transition was executed. I know I have to do some fancy calculations to get the value from New Status - Old Status, but I'm not that advanced in SQL. Has anyone else achieved this?

Thanks

4 answers

1 accepted

Comments Closed
This widget could not be displayed.

Sure thing Marco! I have to give 110% credit to a colleague who ended up solving it! We use Oracle - hopefully it will work for you.

WITH core AS
(select
    -- PK
    jiraissue.pkey KEY,
    --
    to_char(jiraissue.project) project,
--    jiraissue.created,
    dbms_lob.substr(changeitem.oldstring,4000,1) step,
    changegroup.created Executed
--    dbms_lob.substr(changeitem.newstring,4000,1) AS "Destination"
from changeitem
    join changegroup on changeitem.groupid = changegroup.id
    join jiraissue on jiraissue.id = changegroup.issueid
where
--jiraissue.project='10100' AND
 changeitem.field ='status'
--AND jiraissue.pkey ='JRA-1'
--AND jiraissue.pkey ='JRA-2
),

EVENTS AS
(SELECT
    tab.*,
    row_number() over(PARTITION BY key ORDER BY executed) myrank
FROM
    (SELECT * FROM core
    UNION ALL
    SELECT jiraissue.pKEY, to_char(jiraissue.project), 'Ticket Created', jiraissue.created
    FROM jiraissue
--    JOIN core ON core.KEY=jiraissue.pKEY
    WHERE jiraissue.pKEY IN (SELECT KEY FROM core)
    UNION ALL
    SELECT DISTINCT KEY, project, 'Terminal', sysdate FROM core
    ) tab
)

SELECT
    -- PK
    e2.key,
    --
    project.pkey project_key,
    e2.step,
    e2.executed,
    round(24*60*(e1.executed-e2.executed),0) transition_time_min
--    e1.KEY
FROM EVENTS e1
    JOIN events e2 ON e1.key=e2.KEY AND e1.myrank=e2.myrank+1
    JOIN project ON e1.project = project.id
--ORDER BY e2.KEY

great, thanks. We are working with MS SQL-Server but I will figure out a way, hopefully :)

Any one using this on a mysql servor please? Thanks

@Marco Melas Was wondering if you got this fixed in MS SQL? Thank you.

I updated the jiraissue.project, also tried updating jiraissue.pkey, but the SQL did not return anything for me. I use JIRA 6.2.2.

This code is far too old for your version.

This widget could not be displayed.

Here is the query that works for me on mysql. The issue is open since long but would be helpful.

Select FIXVERSION, ISSUENUM as ISSUE, STEP2  as 'STATUS', TIMESTAMPDIFF(HOUR, EXECUTED, EXECUTED2) as timeinSTATUS from(
  SELECT * from ( 
    select 
        ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum as ISSUENUM, ci.OLDSTRING AS STEP, cg.CREATED AS EXECUTED, s.pname AS CURRENT_STATUS
      FROM jiraissue ji
      JOIN changegroup cg on cg.issueid = ji.ID
      JOIN changeitem ci on ci.groupid = cg.id and ci.field = "status"
      LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
      LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID 
      JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'WP' -- Put your Project Key here
      JOIN issuestatus s ON ji.issuestatus = s.ID
      WHERE ji.issuetype = 18 -- Put your issue types id here
      UNION
      SELECT  
        ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum, 'Created' AS STEP, ji.CREATED AS EXECUTED, 'something'
      FROM jiraissue ji
      LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
      LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID 
      JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'WP' -- Put your Project Key here
      JOIN issuestatus s ON ji.issuestatus = s.ID
      WHERE ji.issuetype = 18 -- Put your issue types id here
      order by ID, EXECUTED) as times
    cross join
      (SELECT  
        ji.id AS ID2, Y.vname AS FIXVERSION2, ji.issuenum as ISSUENUM2, ci.OLDSTRING AS STEP2, cg.CREATED AS EXECUTED2, s.pname  AS CURRENT_STATUS2
      FROM jiraissue ji
      JOIN changegroup cg on cg.issueid = ji.ID
      JOIN changeitem ci on ci.groupid = cg.id and ci.field = "status"
      LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
      LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID 
      JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'WP' -- Put your Project Key here
      JOIN issuestatus s ON ji.issuestatus = s.ID
      WHERE ji.issuetype = 18 -- Put your issue types id here
      UNION
      SELECT  
        ji.id AS ID2, Y.vname AS FIXVERSION, ji.issuenum, 'Created' AS STEP, ji.CREATED AS EXECUTED2, 'something'
      FROM jiraissue ji
      LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
      LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID 
      JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'WP' -- Put your Project Key here
      JOIN issuestatus s ON ji.issuestatus = s.ID
      WHERE ji.issuetype = 18 -- Put your issue types id here
      order by ID2, EXECUTED2) as times2
    where times2.id2 = times.id and times2.EXECUTED2 in (
      select min(times3.EXECUTED) from (  
        select 
            ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum, ci.OLDSTRING AS STEP, cg.CREATED AS EXECUTED, s.pname AS CURRENT_STATUS
          FROM jiraissue ji
          JOIN changegroup cg on cg.issueid = ji.ID
          JOIN changeitem ci on ci.groupid = cg.id and ci.field = "status"
          LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
          LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID 
          JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'WP' -- Put your Project Key here
          JOIN issuestatus s ON ji.issuestatus = s.ID
          WHERE ji.issuetype = 18 -- Put your issue types id here
        UNION
          SELECT  
            ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum, 'Created' AS STEP, ji.CREATED AS EXECUTED, 'something'
          FROM jiraissue ji
          LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
          LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID 
          JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'WP'
          JOIN issuestatus s ON ji.issuestatus = s.ID
          WHERE ji.issuetype = 18 -- Put your issue types id here
          order by ID, EXECUTED) as times3    
        where times3.id= times2.id2 and times3.EXECUTED>times.EXECUTED
    )
)as final
order by ISSUENUM

You need to change the project key in few lines and the issue ID for which you want the time in status.

I limited this as there are a lot of records to go through if we dont limit it.

Regards,

Vijay

How do you remove the limit? I want to add a condition to only review the status for a certain time period, i.e  and ji.CREATED between '2016-03-20 00:00:00' and '2016-03-20 23:59:59'

 

Nevermind figured it out. Just under the preferences set to No Limit, and just add this line throughout the script. Updated script below:

 

#Time in Status with Creation Filter
Select FIXVERSION, ISSUENUM as ISSUE, STEP2 as 'STATUS', TIMESTAMPDIFF(HOUR, EXECUTED, EXECUTED2) as timeinSTATUS from(
SELECT * from (
select
ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum as ISSUENUM, ci.OLDSTRING AS STEP, cg.CREATED AS EXECUTED, s.pname AS CURRENT_STATUS
FROM jiraissue ji
JOIN changegroup cg on cg.issueid = ji.ID
JOIN changeitem ci on ci.groupid = cg.id and ci.field = "status"
LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'NCFMSHD' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype = 16 -- Put your issue types id here
and ji.CREATED between '2016-02-01 00:00:00' and '2016-02-31 23:59:59'
UNION
SELECT
ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum, 'Created' AS STEP, ji.CREATED AS EXECUTED, 'something'
FROM jiraissue ji
LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'NCFMSHD' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype = 16 -- Put your issue types id here
and ji.CREATED between '2016-02-01 00:00:00' and '2016-02-31 23:59:59'
order by ID, EXECUTED) as times
cross join
(SELECT
ji.id AS ID2, Y.vname AS FIXVERSION2, ji.issuenum as ISSUENUM2, ci.OLDSTRING AS STEP2, cg.CREATED AS EXECUTED2, s.pname AS CURRENT_STATUS2
FROM jiraissue ji
JOIN changegroup cg on cg.issueid = ji.ID
JOIN changeitem ci on ci.groupid = cg.id and ci.field = "status"
LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'NCFMSHD' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype = 16 -- Put your issue types id here
and ji.CREATED between '2016-02-01 00:00:00' and '2016-02-31 23:59:59'
UNION
SELECT
ji.id AS ID2, Y.vname AS FIXVERSION, ji.issuenum, 'Created' AS STEP, ji.CREATED AS EXECUTED2, 'something'
FROM jiraissue ji
LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'NCFMSHD' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype = 16 -- Put your issue types id here
and ji.CREATED between '2016-02-01 00:00:00' and '2016-02-31 23:59:59'
order by ID2, EXECUTED2) as times2
where times2.id2 = times.id and times2.EXECUTED2 in (
select min(times3.EXECUTED) from (
select
ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum, ci.OLDSTRING AS STEP, cg.CREATED AS EXECUTED, s.pname AS CURRENT_STATUS
FROM jiraissue ji
JOIN changegroup cg on cg.issueid = ji.ID
JOIN changeitem ci on ci.groupid = cg.id and ci.field = "status"
LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'NCFMSHD' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype = 16 -- Put your issue types id here
and ji.CREATED between '2016-02-01 00:00:00' and '2016-02-31 23:59:59'
UNION
SELECT
ji.id AS ID, Y.vname AS FIXVERSION, ji.issuenum, 'Created' AS STEP, ji.CREATED AS EXECUTED, 'something'
FROM jiraissue ji
LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN project ON project.ID = ji.PROJECT AND project.pkey = 'NCFMSHD'
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype = 16 -- Put your issue types id here
and ji.CREATED between '2016-02-01 00:00:00' and '2016-02-31 23:59:59'
order by ID, EXECUTED) as times3
where times3.id= times2.id2 and times3.EXECUTED>times.EXECUTED
)
)as final
order by ISSUENUM;

 

This widget could not be displayed.

Hi Amy,

we have exactly the same question. Any progress on your side you would care to share?

Thanks

Marco

This widget could not be displayed.

Where do I have to insert this code?

Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

207 views 3 0
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you