How do i report on the status of issues from on transition to another? i need to report on the statuses from transition to transition and also the OS_historystep, the time in between from one status to the other.

[Mojisola Koretimi]

I used the query below but the result of the query is repeating data, i got data repeating or duplicating. Thank you

SELECT J.ID AS "ISSUE ID", J.PKEY AS "RFS NUMBER", J.SUMMARY, J.DESCRIPTION, J.ISSUETYPE,
J.REPORTER, U2.FULL_NAME AS "REPORTER FULL_NAME", J.ASSIGNEE,U.FULL_NAME AS "ASSIGNEE FULL_NAME", PROJECT."PROJECT LEAD", PROJECT."PROJECT NAME", PROJECT."PROJECT LEAD FULLNAME", ISSUETYPE.PNAME AS "ISSUE TYPE NAME", ISSUESTATUS.PNAME AS "ISSUE_STATUS NAME",
J.CREATED AS "ISSUE CREATED DATE", J.UPDATED AS "ISSUE UPDATED DATE" ,J.DUEDATE AS "ISSUE DUE DATE",
CFV.NUMBERVALUE AS "ESTIMATE" , CFV2.DATEVALUE AS "COMPANY",
CFV3.DATEVALUE AS "DEPARTMENT",

chg.oldstring AS "OLD TRANSITION STATUS", chg.newstring AS "NEW TRANSITION STATUS",

chg.created AS "ACTIVITY_TRANSITION DATE", S.START_DATE AS "TRANSITION START_DATE",

S.FINISH_DATE AS "TRANSITION FINISH_DATE", chg.author AS "TRANSITION EXECUTER",

chg.field AS "TRANSITION FIELD"

FROM JIRAISSUE J

LEFT OUTER JOIN v_CHANGEGROUP CHG ON J.ID = CHG.ISSUEID AND CHG.FIELD= 'status'

LEFT OUTER JOIN OS_HISTORYSTEP S ON J.WORKFLOW_ID =S.ENTRY_ID

LEFT OUTER JOIN V_USERBASE U ON J.ASSIGNEE = U.USERNAME

LEFT OUTER JOIN V_USERBASE U2 ON J.REPORTER = U2.USERNAME

LEFT OUTER JOIN PRIORITY PR ON PR.ID = J.PRIORITY

LEFT OUTER JOIN ISSUETYPE ON (J.ISSUETYPE = ISSUETYPE.ID)

LEFT OUTER JOIN ISSUESTATUS ON (J.ISSUESTATUS = ISSUESTATUS.ID)

JOIN V_PROJECT PROJECT ON (PROJECT."PROJECT ID" = J.PROJECT)

LEFT OUTER JOIN v_CUSTOMFIELDVALUE CFV
ON (J.ID = CFV.ISSUE AND CFV.CFNAME ='Estimate (Days)')

LEFT OUTER JOIN V_CUSTOMFIELDVALUE CFV2
ON (J.ID = CFV2.ISSUE AND CFV2.CFNAME = 'Company')

LEFT OUTER JOIN V_CUSTOMFIELDVALUE CFV3
ON (J.ID = CFV3.ISSUE AND CFV3.CFNAME ='Department')


ORDER BY J.PKEY, CHG.CREATED

[Paul Nowak]

Thanks but that doen't help much. Also I have found that the Query only returns the statuses linked to a work flow which means you miss statuses when there was no workflow.

To get all the statuses movements of a JIRA item I found I only had to use the following tables:-

Jiraissue
changegroup
changeitem
userbase
propertyentry
propertystring

[Sergey Papurin]

select distinct

group by J.SUMMARY

[Paul Nowak]

How are you views created?