Get issues by sprint from database?

[Philip Chiappini]

I'm trying to get issues per sprint (past/present/future) from our MySQL db (I've linked it to SQL Server so that I can do recursive queries).

I've followed this thread as far as it can take me: https://answers.atlassian.com/questions/96142/how-to-get-sprint-issue-list-by-sql

It gets me a ranked list of all issues but only sprint markers for the current sprint.

From there I don't know where I can find the different boards in the db and markers within them to potentially find what sprint issues are sorted into.

Any ideas? It's got to be possible, right?

[richie_gee · Accepted solution]

For you question, it is actually answered on the last post of the link which is the information is actually stored on the active object table below:

select ID,NAME,RAPID_VIEW_ID from AO_60DB71_SPRINT;

As for the boards, it should be the rapid_view_id column at the above table

To determine the list of sprints on particular board you can use the query below:

select "ID","NAME" from "AO_60DB71_SPRINT" where "RAPID_VIEW_ID" = (select "ID" from "AO_60DB71_RAPIDVIEW" where "NAME" like '%boardname%');

Then it is rather easy fron here to determine which sprint you want to retrieve from the database which can be fetched from the query:

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

Customfield ID is retrieved from the below query:

select * from customfield where cfname like '%Sprint%';

And the string value is the sprint value that you have sorted on the first or second query.

Hope this helps, cheers

[Brian Hayden]

Hi, I see the "RAPID_VIEW_ID" field is referenced in the SPRINT table but in JIRA Database that I am connected to, I dont have that field in SPRINT table. This just makes my job harder because I am struggling to join RAPID_VIEW and SPRINT table.

[Teresa Noguero]

I thought I would add how I did:

This query give me the list of boards and the sprints for each one:

select RW.ID AS BOARD_ID,RW.NAME AS BOARD_NAME,SP.ID as SPRINTID,SP.NAME, SP.COMPLETE_DATE, SP.END_DATE, SP.START_DATE, SP.STARTED, SP.SEQUENCE 

from AO_60DB71_SPRINT SP INNER JOIN dbo.AO_60DB71_RAPIDVIEW RW ON SP.RAPID_VIEW_ID = RW.ID ORDER BY BOARD_ID

This adds the issues for each sprint in a board:

SELECT  RW.ID AS BOARD_ID,RW.NAME AS BOARD_NAME,SP.ID as SPRINTID,SP.NAME, 
SP.COMPLETE_DATE, SP.END_DATE, SP.START_DATE, SP.STARTED, SP.SEQUENCE, 
CFV.ISSUE AS JIRAISSUE_ID FROM [Novedad].[dbo].[customfieldvalue] CFV 
INNER  JOIN [Novedad].[dbo].[customfield] CF ON CF.ID = CFV.CUSTOMFIELD 
INNER JOIN [Novedad].[dbo].[AO_60DB71_SPRINT] SP ON  
cast(SP.ID as varchar) = CFV.STRINGVALUE  
INNER  JOIN dbo.AO_60DB71_RAPIDVIEW RW ON SP.RAPID_VIEW_ID = RW.ID 
WHERE  cfname like '%sprint%' ORDER BY BOARD_ID

if you want you can add the project table and jiraissue table to select one particular project and display the key of the issue as below.  

SELECT   PR.PKEY + '-'+  CAST(JI.ISSUENUM AS VARCHAR) AS ISSUEKEY, CFV.ID, SP.NAME 
FROM [Novedad].[dbo].[customfieldvalue] CFV
INNER JOIN [Novedad].[dbo].[customfield] CF ON CF.ID = CFV.CUSTOMFIELD 
inner join [Novedad].[dbo].jiraissue JI   on JI.ID = CFV.ISSUE 
INNER JOIN [Novedad].[dbo].project PR  ON JI.PROJECT = PR.ID
INNER JOIN [Novedad].[dbo].[AO_60DB71_SPRINT] SP ON  SP.ID = CFV.STRINGVALUE 
WHERE PR.PKEY = 'SD' AND cfname like '%sprint%'

[Herman Esterhuizen]

Can you help with add Sub-Tasks as well please at the moment it only show Story's and Bugs but we add Sub-Tasks under our Stories 

[Gregory Kneller]

 Here is the code that works in jira 7 (ms sql )

SELECT b.NAME SprintBoardName, b.ID SprintBoardID, S.NAME SprintName, s.ID sprintID, p.pkey+'-'+cast(i.issuenum as NCHAR(10)) IssueKey, p.pkey,
s.STARTED, 
CASE WHEN s.END_DATE is not null then 'Yes' ELSE 'No' END Completed

 FROM customfieldvalue c, jiraissue i, project p, AO_60DB71_SPRINT s left join AO_60DB71_RAPIDVIEW b on b.ID=s.RAPID_VIEW_ID where i.project=p.id and i.ID=c.ISSUE 
 and CUSTOMFIELD = 10000 and s.ID=CAST(c.STRINGVALUE as int) 

For full research of  usage of sprints by boards and projects

[Amit Kumar Sharma]

Really Helpful