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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I am interested to understand how you managed to get this working?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.