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:-
Good Day, Bad bad traffic, not sure why!!!! 1/2 hour commute took me 2 hours today 🤯 What helped me is that I kept browsing LinkedIn until...
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
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs