I need to query JIRA to get a report of issues assigned versus resolved by user in a timeframe (say per month). This resembles the gadget Created versus Resolved in Jira.
Any hints how to query?
Ah!
You should try this (MS/SQL):
SELECT ji.assignee, Count(*) 'tickets', (SELECT Count(*) FROM jiraissue ja WHERE ja.assignee = ji.assignee AND ja.resolutiondate IS NULL AND ja.created >= CONVERT(DATETIME, '01.11.2014', 104) AND ja.created < CONVERT(DATETIME, '05.11.2014', 104)) 'open', (SELECT Count(*) FROM jiraissue ja WHERE ja.assignee = ji.assignee AND ja.resolutiondate IS NOT NULL AND ja.created >= CONVERT(DATETIME, '01.11.2014', 104) AND ja.created < CONVERT(DATETIME, '05.11.2014', 104)) 'resolved', CONVERT(DATETIME, '01.11.2014', 104) 'from (inclusive)', CONVERT(DATETIME, '05.11.2014', 104) 'to (exclusive)' FROM jiraissue ji WHERE ji.assignee IS NOT NULL AND created >= CONVERT(DATETIME, '01.11.2014', 104) AND created < CONVERT(DATETIME, '05.11.2014', 104) GROUP BY ji.assignee HAVING Count(*) > 0;
Be sure to replace all from and to dates to your needs.
Cheers, Mario
Hi Adolfo,
I'm not sure if I correctly understand, but are you searching for something like that:
resolved > startOfMonth(-3) AND resolved < startOfMonth()
-3 means -3 month
Cheers,
Mario
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Not quite, I need to group by user.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you want that behaviour for each single user logged in you could use "currentUser()" in your query. If you need to overview all users Im not sure how to do it. However this would be the "by logged in user"-Query: resolved > startOfMonth(-3) AND resolved < startOfMonth() AND assignee = currentUser() AND reporter = currentUser() ORDER BY resolved DESC
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks, but what I actually need is a SQL query.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.