I'm using actually Jira 5.1.6 and GreenHopper 6.0.5. My database is Postgresql.
For reporting purpose I need to list the issues that are planned in the current sprint and in the upcoming sprints.
Issues are in jiraissue table, project in project, version are in projectversion and the association between issues and projectversions is nodeassociation table.
But How can I link the issues with the sprints. I the sprints description in the tables AO_60DB71_SPRINT and AO_60DB71_SPRINTMARKER. I'm stuck the association between the two.
Any help will be greetly appreciated.
Here's some sqlserver code:
DECLARE @CUSTOM_FIELD_ID int = 13000; WITH list (position, id, issue, "next") as ( SELECT 0 AS position, rang.ID, rang.ISSUE_ID, rang.NEXT_ID FROM AO_60DB71_ISSUERANKING rang LEFT OUTER JOIN AO_60DB71_ISSUERANKING rang2 ON (rang2.CUSTOM_FIELD_ID = rang.CUSTOM_FIELD_ID and rang2.NEXT_ID = rang.ISSUE_ID) WHERE rang.CUSTOM_FIELD_ID = @CUSTOM_FIELD_ID AND rang2.ID is null UNION ALL SELECT position + 1, rang.ID, rang.ISSUE_ID, rang.NEXT_ID FROM AO_60DB71_ISSUERANKING rang JOIN list ON (rang.ISSUE_ID = list."next") WHERE CUSTOM_FIELD_ID = @CUSTOM_FIELD_ID ) SELECT list.position, ji.pkey as "key", sm.MARKER_CONTEXT AS rapidView, sm.NAME AS sprint FROM list LEFT OUTER JOIN jiraissue ji ON ji.id = list.ISSUE LEFT OUTER JOIN AO_60DB71_SPRINTMARKER sm ON sm.ID = -issue ORDER BY position OPTION (maxrecursion 0);
If you're not very familiar with the JIRA DB this will probably be quite challenging.
Sprints that are in progress can be found by looking at the 'Sprint' custom field in the issues (using the jiraissue, customfield, customfieldvalue and ao_60db71_sprint tables).
Future sprints do not own any issues, instead they are ranked in the linked list of ranked issues. You can find the ranking of items in ao_60db71_issueranking. Negative numbers in the issue_id field represent sprint footers. You can find the list of current sprint footers in ao_60db71_sprintmarker. You need to find all issues that match the filter for your board then check if they are lower in the ranking than the sprint marker you are looking for (and higher than the previous marker).
Malik - I have this exact same need.
Shaun, I read through your comments and they make sense. I've walked through the data in my issueranking table and I thought I had the pattern you describe figured out, but ultimately I'm not able to query for the boundries. Once I can see a coorelation between the UI and the data I think I can figure it out, but not able to get my SQL to match the plan board yet. Could you throw us some basic SQL to get us headed in the right direction?
A more elegant solution to all of this would be for Atlassian to add export support to the plan mode.
It's very painful to reconstruct the linked list of items using SQL because you need a recursive query (which is not supported in all DBs). Here's a sample one that will work in Postgres and will show the absolute rank value, the rank table row id, the issue id, next issue id and issue key in order of rank.
-- Get the linked list of items from the ranking table (recursive, only works in Postgres) with recursive list (position, id, issue, next, key) as ( select 0 as "position", rank."ID", rank."ISSUE_ID", rank."NEXT_ID", ji.pkey from "AO_60DB71_ISSUERANKING" rank left outer join "AO_60DB71_ISSUERANKING" rank2 on rank2."CUSTOM_FIELD_ID" = rank."CUSTOM_FIELD_ID" and rank2."NEXT_ID" = rank."ISSUE_ID" left outer join jiraissue ji on ji.id = rank."ISSUE_ID" where rank."CUSTOM_FIELD_ID" = 10410 and rank2."ID" is null union all select position + 1, "ID", "ISSUE_ID", "NEXT_ID", ji.pkey from "AO_60DB71_ISSUERANKING" join list on "AO_60DB71_ISSUERANKING"."ISSUE_ID" = list.next left outer join jiraissue ji on ji.id = "AO_60DB71_ISSUERANKING"."ISSUE_ID" where "CUSTOM_FIELD_ID" = 10410 ) select * from list;
Some sample data:
As described above, the negative numbered items in the linked list are the future sprint markers. You'd still need to filter down the results to only match the filter for the board you're interested in (and change the CUSTOM_FIELD_ID to match the id of the Rank field in your instance).
Agreed a link to issue navigator from plan mode is a better option here, we do plan to add that in the future.
This query works for me in JIRA 7.0.0. It also retrieves a second user field (story points):
SELECT c.STRINGVALUE AS SprintId, i.issuenum AS IssueNum, i.ID as IssueId, d.NUMBERVALUE as StoryPoints FROM customfieldvalue c, jiraissue i, customfieldvalue d where i.ID=c.ISSUE and c.CUSTOMFIELD = 10004 AND c.STRINGVALUE = 5 and d.CUSTOMFIELD = 10002 and d.ISSUE=i.ID;
You need to replace 10002 (StoryPointsFieldId), 10004 (SprintFieldId) and 5 (SprintId) with your IDs.
Hope this helps, Matthias
I needed a more simple SELECT Statement to just get the Issues of a Sprint in MySQL and Jira Agile 22.214.171.124.
This is my solution:
Get all Sprint-Ids and Names:
select ID,NAME from AO_60DB71_SPRINT;
Now you get something like this:
+----+--------------------------------+ | ID | NAME | +----+--------------------------------+ | 1 | TestSprint 1 | | 2 | TestSprint 2 | +----+--------------------------------+
Now pick a Sprint-ID and use it in the next statement:
SELECT c.STRINGVALUE AS SprintId, i.pkey AS Task FROM customfieldvalue AS c LEFT OUTER JOIN jiraissue i ON (i.ID=c.ISSUE) WHERE CUSTOMFIELD = 10300 AND STRINGVALUE = 2
Here is CUSTOMFIELD = 10300 the Sprint-Customfield. And STRINGVALUE = 2 the Sprint-ID.
you get something like this:
+----------+---------------+ | SprintId | Task | +----------+---------------+ | 2 | XCZ-1439 | | 2 | XCZ-1792 | | 2 | XCZ-1627 | | 2 | XCZ-704 | +----------+---------------+
For me this was important because in some circumstances a sprint had tasks from different projects and most users did not have permissions on these other projects and couldn't start sprints. That way I was able to really see ALL issues assigned to a specific sprint.
Here is an updated Postrgres query, one that does not require you to specify the Ranking field, assuming that you have only on ranking field in your jira.
with recursive list (position, id, issue, next, key) as ( select 0 as "position", rank."ID", rank."ISSUE_ID", rank."NEXT_ID", ji.pkey from "AO_60DB71_ISSUERANKING" rank left outer join "AO_60DB71_ISSUERANKING" rank2 on rank2."CUSTOM_FIELD_ID" = rank."CUSTOM_FIELD_ID" and rank2."NEXT_ID" = rank."ISSUE_ID" left outer join jiraissue ji on ji.id = rank."ISSUE_ID" where rank."CUSTOM_FIELD_ID" = (select id from customfield where customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-global-rank') and rank2."ID" is null union all select position + 1, "ID", "ISSUE_ID", "NEXT_ID", ji.pkey from "AO_60DB71_ISSUERANKING" join list on "AO_60DB71_ISSUERANKING"."ISSUE_ID" = list.next left outer join jiraissue ji on ji.id = "AO_60DB71_ISSUERANKING"."ISSUE_ID" where "CUSTOM_FIELD_ID" = (select id from customfield where customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-global-rank')) select * from list;
This code is to get the list of issues in a sprint for jira 6.3
DECLARE @SprintName nvarchar(255) = 'Sprint 1'; SELECT SprintId = CFV.STRINGVALUE, IssueId = JI.ID, IssueCode = P.pkey + '-' + CAST(JI.issuenum AS nvarchar(10)), NextIssueId = IR.NEXT_ID FROM JiraStore.dbo.customfield CF WITH(NOLOCK) INNER JOIN JiraStore.dbo.customfieldvalue CFV WITH(NOLOCK) ON CFV.CUSTOMFIELD = CF.ID INNER JOIN JiraStore.dbo.jiraissue JI WITH(NOLOCK) ON JI.ID = CFV.ISSUE INNER JOIN JiraStore.dbo.project P WITH(NOLOCK) ON P.ID = JI.PROJECT INNER JOIN JiraStore.dbo.AO_60DB71_SPRINT S WITH(NOLOCK) ON CAST(S.ID AS nvarchar(10)) = CFV.STRINGVALUE AND S.NAME = @SprintName INNER JOIN JiraStore.dbo.AO_60DB71_ISSUERANKING IR WITH(NOLOCK) ON IR.ISSUE_ID = JI.ID WHERE CF.cfname = 'Sprint' ;
JiraStore.dbo. needs to be replaced with your database name in your SQL server instance.
On Oracle, try:
WITH issueranking AS ( SELECT * FROM AO_60DB71_ISSUERANKING WHERE custom_field_id = your_value_here ), ranked_issues AS ( SELECT issueranking.custom_field_id, issueranking.id, issueranking.issue_id, issueranking.next_id, project.pkey || '-' || jiraissue.issuenum AS pkey, LEVEL FROM issueranking LEFT OUTER JOIN jiraissue ON jiraissue.id = issueranking.issue_id LEFT OUTER JOIN project ON project.id = jiraissue.project START WITH issueranking.id = ( SELECT first_issue.id FROM issueranking first_issue WHERE NOT EXISTS ( SELECT NULL FROM issueranking all_issues WHERE all_issues.next_id = first_issue.issue_id ) ) CONNECT BY PRIOR next_id = issue_id ORDER BY LEVEL ) SELECT issue_id, pkey, ROWNUM -1 AS globalrank FROM ranked_issues WHERE issue_id > 0
I currently only seem to have one value for custom_field_id in my AO_60DB71_ISSUERANKING table, so I don't really need the first WITH clause. But better safe than sorry, I suppose!
we're facing the same problem. It woult be very nice,if anybody can translate this SQL-Statement to a mySQL-Statement, because mySQL doesn't support functions like "with" and my SQL-Skills are not that good, that I can do this by myself.
I please have a follow up question. I need to link issue to Kanban Boards. The above code associates sprints to issues. Currently I cannot find a way to link Issues to Kanban boards in the database.
My starting point to look was the Rapidview table but I cannot find tables that would join this table to the jiraissue table.
Kind Regards, Drickus
There is no link.
This is one of hundreds of reasons you should forget SQL when looking at Jira - it is the worst possible approach to doing anything in Jira.
To explain why there is no link between issue and board in the database, it is because you do not need one. A board is a view of a set of issues, which is determined by the board's filters. For a Kanban board, you need to combine the two filters and run the resultant query to get the list of issues currently on the board (the main filter says what issues to return in general and the secondary filter then excludes issues that are removed from the board because they are considered "done")
See how to use Jira on the go! Demo Den Episode 5 is here: meet Jira Mobile with Jira Product Manager Rayen Magpantay. Demo Den is in our monthly series where a Jira PM demonstrates...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events