Get "time in source status" value using SQL

[Amy Biasella]

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

[Amy Biasella · Accepted solution]

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

[Marco Melas]

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

[YassineM]

Any one using this on a mysql servor please? Thanks

[Jo Nelson]

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

[Royce Wong]

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.

[Nic Brough -Adaptavist-]

This code is far too old for your version.

[Vijay Khacharia]

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

[Raed Muslimani]

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'

[Raed Muslimani]

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;

[Isabell Chittka]

Where do I have to insert this code?

[Marco Melas]

Hi Amy,

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

Thanks

Marco