Get issues by sprint from database?

Philip Chiappini October 22, 2013

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?

4 answers

1 accepted

2 votes
Answer accepted
richie_gee
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 4, 2013

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 August 28, 2014

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.

2 votes
Teresa Noguero November 9, 2015

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 July 12, 2018

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 

0 votes
Gregory Kneller
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 21, 2017

 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

0 votes
Amit Kumar Sharma November 13, 2015

Really Helpful

Suggest an answer

Log in or Sign up to answer