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

Question

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

Olga Videc · Answer

Check this link

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

Nic Brough -Adaptavist- · Answer

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 · Answer

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