How to find out sprint value for sub-tasks in Jira
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Although you should be able to view the sprint value of a sub-task in the Jira UI when searching the database this value does not exist.
Normally the sprint value is stored in the customfieldvalue table, one row for each issue. However, sub-tasks are a special case and do not have this value. This happens because the sub-tasks necessarily inherit the Sprint Value directly from the parent issue.
Solution
The following SQL query should retrieve a list of sub-tasks with their sprintID's
create temp table tbl as
select cfv.issue as Issue_ID, cfv.stringvalue as SPRINT_ID
from customfieldvalue cfv
join customfield cf on cf.id=cfv.customfield
join jiraissue ji on ji.id=cfv.issue
join "AO_60DB71_SPRINT" s on s."ID"=cfv.stringvalue::integer
join "AO_60DB71_RAPIDVIEW" b on s."RAPID_VIEW_ID" = b."ID"
where cf.cfname='Sprint';
select j.id,j.summary ,j.description, i.pname, p.pname, il."source", tbl.SPRINT_ID
from jiraissue j
inner join issuetype i on j.issuetype = i.id
inner join project p on j.project = p.id
inner join issuelink il on j.id = il.destination
inner join issuelinktype ilt on il.linktype = ilt.id
inner join tbl on il."source" = tbl.Issue_ID
where i.pname = 'Sub-task';
drop table tbl;
This query was tested with a PostgreSQL database. Other Databases may require some adjustments.