nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

richie_gee

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

All comments

richie_gee

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%'

Amit Kumar Shar

Really Helpful

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

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

issue

jira

jira-administration

sprint

database

ms-sql

mysql