Time spend in a Status, MSSQL extract for PowerBi

Dennis Hoeyrup April 26, 2017

I'm trying to get data from the Jira DB (SQL Server ie. MSSQL), so I can tell how long a time was spend in a given case status.
As you all might know that information is provided in the Jira interface by a plugin and not as a stored value in the DB.

I found an Oracle SQL script online, which I then converted to a MSSQL script to the best of my ability.. which it seems is a bit lacking, considering that I'm not used to MSSQL.

The script (below) gives me no result. I've narrowed it down to an issue with one of the last JOINS ie.

JOIN events e2 ON e1.NG =e2.NG AND e1.myrank=e2.myrank+1

Where the script re-joins EVENTS with itself to be able to get two rows (the two execution times that needs to be subtracted to get time spend in a status)

Any help or suggestion will be very welcome.. this is driving me nuts :(

Note: the whole point is to use the script in MS PowerBI.


WITH core AS
(select
         jiraissue.pkey NG,
         convert(varchar, jiraissue.project) project,
        SUBSTRING(changeitem.oldstring,4000,1) step,
        changegroup.created Executed
from changeitem
        join changegroup
        on
        changeitem.groupid = changegroup.id
        join jiraissue
        on
        jiraissue.id = changegroup.issueid
where
   changeitem.field ='status'
),

EVENTS AS
(SELECT
           tab.*,
           row_number() over(PARTITION BY NG ORDER BY executed) myrank
FROM
(SELECT * FROM core
UNION ALL
          SELECT jiraissue.pKEY,
          convert(varchar, jiraissue.project),
         'Ticket Created',
          jiraissue.created
FROM jiraissue
WHERE
           jiraissue.pKEY IN (SELECT NG FROM core)
UNION ALL
SELECT DISTINCT NG, project, 'Terminal', getdate() FROM core
) tab
)

SELECT
  e2.NG,
  project.pkey project_key,
  e2.step,
  e2.executed,
  round(24*60*(convert(int,e1.executed)-convert(int,e2.executed)),0)   transition_time_min

FROM EVENTS e1
JOIN events e2
ON
e1.NG =e2.NG AND e1.myrank=e2.myrank+1
JOIN project
ON
e1.project = project.id

3 answers

0 votes
Dennis Hoeyrup April 28, 2017

In case other MSSQL users are interested, here's the revision I made onto a script I found online.

MANY THANKS to the donor providing the base code.

Select FIXVERSION,
ISSUENUM as ISSUE,
STEP2 as 'STATUS',
round(24*60*60*(convert(int,EXECUTED2)-convert(int,EXECUTED)),0) as TimeInSecondsSTATUS,
EXECUTED,
EXECUTED2
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
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype > 0 -- Put your issue types id here
UNION ALL
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
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype > 0 -- Put your issue types id here
) 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 = 'xxx' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype > 0 -- Put your issue types id here
UNION ALL
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 = 'xxx' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype > 0 -- Put your issue types id here
) 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 = 'xxx' -- Put your Project Key here
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype > 0 -- Put your issue types id here
UNION ALL
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 = 'xxx'
JOIN issuestatus s ON ji.issuestatus = s.ID
WHERE ji.issuetype > 0 -- Put your issue types id here
) as times3
where times3.id= times2.id2 and times3.EXECUTED > times.EXECUTED
)
)as final

 

 

 

 

0 votes
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 27, 2017

Can PowerBi work with Excel or CSV input files?

If so, the Better Excel Plugin could help you with this. It has a workflow-analisys.xlsx template that contains various time metrics, also including one "Time in <status name here>" for each status.

https://marketplace.atlassian.com/plugins/com.midori.jira.plugin.betterexcel/server/overview

Dennis Hoeyrup April 27, 2017

Yes, it can.

Thanks for another option!

0 votes
Dennis Hoeyrup April 26, 2017

Never mind.

I found a different way of doing it..

Rob Conacher September 21, 2021

I am interested to understand how you managed to get this working?

Suggest an answer

Log in or Sign up to answer