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

This widget could not be displayed.

Never mind.

I found a different way of doing it..

This widget could not be displayed.

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!

This widget could not be displayed.

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 Sign up to answer
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...

181 views 2 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