Time spend in a Status, MSSQL extract for PowerBi

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

Never mind.

I found a different way of doing it..

0 vote

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

Yes, it can.

Thanks for another option!

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

 

 

 

 

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,203 views 13 19
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot