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.

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

3 answers

How are you views created?

select distinct

group by J.SUMMARY

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in United States

Topic Tuesday: What's your favorite topic?

Good morning All, Our goal is to get you into the habit of while enjoying your favorite morning drink you are checking the NOVA "space" for topics and comments. Your input is really needed and...

57 views 4 0
View post

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