Hi,
For retrieving transitions tab, use the following query for ms sql server. Field time in source status also included.
With Transitions as
(select row_number() over (PARTITION BY JI.ID order by CG.CREATED asc) row#,JI.ID as ID,
CI.OLDSTRING as FromStatus, CI.NEWSTRING as ToStatus,
CG.CREATED as EXECUTED, JI.CREATED as CREATED
from testdb.dbo.jiraissue JI
join testdb.dbo.changegroup CG on CG.issueid = JI.ID
join testdb.dbo.changeitem CI on CI.groupid=CG.id and field = 'status'
LEFT JOIN testdb.dbo.nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN testdb.dbo.projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN testdb.dbo.project ON project.ID = ji.PROJECT AND project.id = ?
JOIN testdb.dbo.issuestatus s ON ji.issuestatus = s.ID
where ji.issuetype=?)
select A.row#,A.ID as ID,CAST(A.FromStatus AS NVARCHAR(MAX)) as FromStatus,CAST(A.ToStatus AS NVARCHAR(MAX)) as ToStatus,
datediff(SECOND, B.EXECUTED,A.EXECUTED) as timeinseconds,A.EXECUTED as LastExecutionDate from Transitions A
join Transitions B on A.ID=B.ID and (B.row#=A.row#-1)
union
/* -- First Transition [Time In Source Status] --*/
select C.row#,C.ID as ID,CAST(C.FromStatus AS NVARCHAR(MAX)) as FromStatus,CAST(C.ToStatus AS NVARCHAR(MAX)) as ToStatus,
datediff(SECOND, C.CREATED,C.EXECUTED) as timeinseconds,C.EXECUTED as LastExecutionDate from Transitions C where C.row#=1
union
/* -- #Optional -- IssueCreation <to> First Transition Time In Source Status --*/
select D.row#,D.ID as ID,CAST('Created' AS NVARCHAR(MAX)) as FromStatus,CAST(D.FromStatus AS NVARCHAR(MAX)) as ToStatus,
datediff(SECOND, D.CREATED,D.CREATED) as timeinseconds,D.EXECUTED as LastExecutionDate from Transitions D where D.row#=1;
Regards
Arun
