Skip to content

Instantly share code, notes, and snippets.

@pastuhov
Created June 8, 2021 05:53
Show Gist options
  • Save pastuhov/d818bef6422158d992964b9438598fb6 to your computer and use it in GitHub Desktop.
Save pastuhov/d818bef6422158d992964b9438598fb6 to your computer and use it in GitHub Desktop.
jira db query - completed tasks & story points
SELECT count(t.ID) AS "completed tasks",
CAST(sum(c.NUMBERVALUE) AS unsigned integer) AS "story points",
coalesce(user.display_name, ASSIGNEE) AS display_name,
ASSIGNEE AS jiraname
FROM jira.jiraissue t
JOIN customfieldvalue AS c ON c.ISSUE = t.ID
AND c.CUSTOMFIELD = 10008
JOIN customfieldvalue AS s ON s.ISSUE = t.ID
AND s.CUSTOMFIELD = 10000
JOIN ao_60db71_sprint AS sprint ON sprint.ID = s.STRINGVALUE
LEFT JOIN cwd_user AS USER ON ASSIGNEE = user.user_name
WHERE PROJECT IN
(SELECT project.id
FROM nodeassociation
LEFT JOIN project ON project.id = nodeassociation.SOURCE_NODE_ID
WHERE sink_node_entity='ProjectCategory'
AND sink_node_id = 10002)
AND sprint.NAME = '{{sprint_name}}'
-- AND RESOLUTIONDATE IS NOT NULL
AND issuestatus = 10103
GROUP BY ASSIGNEE
ORDER BY sum(c.NUMBERVALUE) DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment