SQL Query for Sprint Issues Added and Removed

[Vineeth Vasudevan]

Hello,

Is there a SQL way to retrieve Issues added or removed from a sprint? I am not looking for JQL options.

[David Nickell]

Best I can tell, you need to look in two places:

1. Issues created after the Start of the Sprint that are tied to your sprint.  
2. Issues that reflect a Sprint Change in the Change Log

For #1 (Issues Created After the Start of the Sprint)

SELECT 
b.NAME SprintBoardName, 
b.ID SprintBoardID, 
S.NAME SprintName, 
s.ID sprintID,
convert(date,DATEADD(SECOND, s.start_date / 1000, '19700101 00:00')) SprintStart,
p.pkey+'-'+cast(i.issuenum as NCHAR(10)) IssueKey, 
i.id IssueId,
convert(date, i.created) IssueCreated,
convert(date,i.RESOLUTIONDATE) IssueResolutionDate
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 c.CUSTOMFIELD = 10000 
and s.ID=CAST(c.STRINGVALUE as int) 
and (i.RESOLUTIONDATE is null or i.resolutiondate >= DATEADD(m, -6, current_timestamp))
and (s.start_date is null or convert(date,DATEADD(SECOND, s.start_date / 1000, '19700101 00:00')) >= DATEADD(m, -6, current_timestamp))
and convert(date,DATEADD(SECOND, s.start_date / 1000, '19700101 00:00')) < convert(date,i.created)

For #2 - Issues that have Sprint values Changed.  (this example pulls all Sprint Changes for the last 6 months)

SELECT 
p.pkey+'-'+cast(i.issuenum as NCHAR(10)) IssueKey, 
i.id IssueId,
convert(date,cg.created) as ChangeDate, 
case when ci.oldstring is null then ' ' else ci.oldstring End SprintOld,
case when ci.newstring is null then ' ' else ci.newstring End SprintNew
FROM jiraissue i,
project p, 
changeitem ci,
changegroup cg
where 
(i.RESOLUTIONDATE is null or i.resolutiondate >= DATEADD(m, -6, current_timestamp))
and i.project=p.id 
and cg.issueid = i.id
and ci.groupid = cg.id
and ci.field = 'Sprint'

[Nic Brough -Adaptavist-]

No, the data is not recorded in the database.  Unless it's a scope change in an active sprint.

[Vineeth Vasudevan]

Yes, I am referring to once the sprint starts, issues added and removed from an active sprint. Is there a way to query it?