I'm currently trying to generate a report for the amount of tickets each status had on any given day. So far, I can generate this report/table for a single project on a single given day.
I'd like to generate a table though that has all days with the amounts of tickets in each status.
I'm using PostgreSQL and this is the query so far:
SELECT count(*), STEP.STEP_ID
FROM (SELECT STEP_ID, ENTRY_ID
WHERE OS_CURRENTSTEP.START_DATE < '04.07.2017'
UNION SELECT STEP_ID, ENTRY_ID
WHERE OS_HISTORYSTEP.START_DATE < '04.07.2017'
AND OS_HISTORYSTEP.FINISH_DATE > '04.07.2017' ) As STEP,
(SELECT changeitem.OLDVALUE::numeric AS VAL, changegroup.ISSUEID AS ISSID
FROM changegroup, changeitem
WHERE changeitem.FIELD = 'status'
AND changeitem.GROUPID = changegroup.ID
UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
FROM jiraissue) As VALID,
jiraissue as JI
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
AND JI.project = <'PROJECT ID HERE'>
Group By STEP.STEP_ID
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
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot