I have the following Issue Filter in JQL which gives me my backlog by rank order:
project = XXX and (type = Story or type = bug) and status != Closed order by rank asc
I'd like to have an equivilant SQL query so I can pull the same data directly in to an Excel spreadsheet. I know "Rank" is a linked list which requires some sort of a recursive SQL query.
Can anybody provide and example so I don't have to wrack my brain trying to figure this out myself.
This will give you a list of all the ids of the requested jiraissues and their current ranking:
select i.id,r.id from jiraissue i, AO_60DB71_ISSUERANKING r, project p where i.id=r.issue_id and i.PROJECT=p.id and p.PKEY = 'JC' and i.ISSUESTATUS not in (select id from ISSUESTATUS where pname in ('Closed')) and i.ISSUETYPE not in (select id from ISSUETYPE where PNAME in ('Story','Bug')) order by r.id;
Just change the 'JC' to your own project key.
should be
... and i.ISSUETYPE in (select ...
(remove the not)
since you want Story and Bug
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
While this SQL works, it's not what I need. I'm not sure what it's giving me, but the list I get back doesn't match the backlog in Greenhopper. I thought since RANK is a linked list you couldn't just order by RANK.id?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SQL for JIRA does the job. Simply perform a query similar to this:
select * from issues where JQL='project = XXX and (type = Story or type = bug) and status != Closed order by rank asc'
Simple?
Of course, you have to read the plug-in documentation in order to learn hot to export to a CSV format, but it is pretty easy too!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That's what I'm currently doing. However, as I make updates in JIRA it's a pain to have to export every time. I'd rather have real time access to my changes by being able to run a query against the backend database directly.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Why don't you just export your JQL results in Excel?
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.