Time spend in a Status, MSSQL extract for PowerBi

Question

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

Rob Conacher · Answer

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

Dennis Hoeyrup · Answer

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

Dennis Hoeyrup · Answer

Yes, it can.

Thanks for another option!