I have created a chart to show a count of specific components for all teams (that have those components) in a specific date range by month.
It seems my query is giving all the issues for each team in the date range. It's not making use of the component filter. The result selects the correct Date Range, Issue Status, Status Category but doesn't filter on the Components.
Are you able to see what the problem is? Below is my query:
1.SELECT DATE_FORMAT (‘Issue cycle time’.’work ended at’,’yyyy-mm’) AS ‘Month of Work ended at’,
2. ‘Project’.’name’ AS Project Name’,
3. COUNT(‘Project component’.’name’) AS ‘ Count of all Name’
4.FROM ‘jira_issue_cycle_time’ AS ‘Issue cycle time’
5.INNER JOIN ‘jira_issue’ AS “Issue’ ON “Issue cycle time’.’issue_id’ = ‘Issue’.’issue_id’
6.INNER JOIN ‘jira_issue_status_history’ AS ‘Issue status history’ ON Issue status history’ ’.’issue_id’ = ‘Issue’.’issue_id’
7.INNER JOIN ‘jira_issue’ AS “Issue’ ON “Issue cycle time’.’issue_id’ = ‘Issue’.’issue_id’
8.INNER JOIN ‘jira_project_component’ AS ‘Project component’ ON ‘Project component’.’project_id’ = ‘Project’.’project_id’
9.WHERE ‘Issue status history’.’ended_at’ >= TIMESTAMP(‘2023-11-01’)
10. AND ‘Issue status history’.’ended_at’ < TIMESTAMP({TODAY}) + INTERVAL 1 DAY)
11. AND (‘Issue’.’status_category’ = ‘Done’)
12. AND ‘Issue’.’status’ IN (‘Done’,’TEAM DONE’)
13. AND ‘Project component’.’name’ IN (‘TDEBT’,’Rfactor’)
14.GROUP BY DATE_FORMAT(‘Issue cycle time’.’work_ended_at’,’yyyy-MM’) , ‘Project’.’name’
15.ORDER BY COUNT(‘Project component’.’name’) ASC, DATE_FORMAT (‘Issue cycle time’.’work_ended_at’,’yyyy-MM’) ASC, Project’.’name’ ASC
16.LIMIT 100000;
