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
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:-
We're looking for participants for another workshop at Atlassian! We need Jira admins who have interesting custom workflows, issue views, or boards. Think you have a story to sh...
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG