query epic

[Shahar Shifman]

Hi there,

Using SQL or JQL, how can i get a number that will give me the progress of an epic  (counting on stories status - open or closed and upon their story points - to know the epic's progress in a more precise way).

Thanks alot in advance,

Shahar.

[Peter Geshev]

Hi Shahar, 

The following SQL query lists all the stories of an epic and their story points value and status category

SELECT s."STATUSCATEGORY", cf.NUMBERVALUE, i.* 
FROM "JIRAISSUE" i
JOIN "ISSUELINK" il ON il."LINKTYPE" = 10200 AND il."SOURCE" = 10000 AND il."DESTINATION" = i."ID"
JOIN "ISSUESTATUS" s ON s."ID" = i."ISSUESTATUS"
JOIN "CUSTOMFIELDVALUE" cf ON cf."ISSUE" = i."ID" AND cf.CUSTOMFIELD = 10006

Where 10006 is the id of the StoryPoints custom field, 10200 is the id of the Epic Story Link type and 10000 is the id of a specific epic. I leave the aggregation to you. However, if you don't wanna deal with sql and you are allowed to use REST calls see this thread

Regards,

Peter