Hey there,
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
FROM OS_CURRENTSTEP
WHERE OS_CURRENTSTEP.START_DATE < '04.07.2017'
UNION SELECT STEP_ID, ENTRY_ID
FROM OS_HISTORYSTEP
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
