How to find out sprint value for sub-tasks in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Listing sub-tasks sprint value
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.
Was this helpful?