Trying to get all issues which belongs to specific Sprint (SQL Query)

[Santosh V]

Hi 

I have used below scripts to get all issues (only Sub-task) under specific sprint 

select it.pname, cfv.issue, cfv.stringvalue as SprintNumber, ao."NAME" as SprintName, cf.cfname, issue.IssueNum as IssueKey, ist.pname
from CustomFieldValue cfv
inner join CustomField CF on CF.Id = CFV.CustomField
inner join jiraissue issue on issue.id = cfv.issue
inner join Project P on issue.Project = P.Id
inner join "AO_60DB71_SPRINT" ao on ao."ID" = Cast(cfv.stringvalue AS bigint)
inner join issuetype it on it.id = issue.issuetype
inner join issuestatus ist on ist.id = issue.issuestatus
where cf.cfname='Sprint'
and ao."NAME" = 'mysprint'
and it.pname = 'Sub-task'

But above query returns only 8 records which is not correct.

It gave 61 records in JIRA application

JQL => project = ABCD AND Sprint = 39614 and issuetype = Sub-task

it would be great and very thank full if some one help me to resolve the issue

Regards

Santosh

[Nic Brough -Adaptavist-]

It would be better not to use SQL, as you can see, the database is not designed for reporting or direct access.

One of things you've run into is that sub-tasks do not belong to sprints.  They go into them as their parents are selected for a sprint and it's their parents that belong to it.  I'd suggest going with Olga's suggestion, as that uses the logic in the application to find them.

[Olga Videc]

Hello @Santosh V 

Do you have scriptrunner? Because of this function.

Sprint has a key right? Just swap TEST-123 with your sprint key.

 issueFunction in subtasksOf("\"Epic Link\"=TEST-123")

BR, Olga

[Olga Videc]

Check this link 

https://www.google.com/amp/s/mraddon.blog/2015/10/28/script-jql-functions-included-functions-in-groovy-scriptrunner/amp/