Committed values for past Sprints

[Vince Crandall]

We recently upgraded to JIRA 6.2 from Jira 5.1 - after the upgrade the Velocity charts lost the column for Committed Story Points.

I am trying to run a report which shows committed vs completed for our 10 projects but need to know which fields in the SQL database to use.

Can I get an sample SQL used to generate the report behind the Velocity report in Agile? This will help greatly in my reporting.

Thanks

[david bachowski]

I'm working on this same thing. Here's a query that will grab you all of the issues, story points, and sprint dates for each sprint started between a certain time period. You should be able to modify this to at least get the number of points completed in a sprint. I still haven't found out how to get commitment yet.

SELECT 
	y.PNAME AS TYPE , 
	pj.pkey +'-' + CAST(i.issuenum AS VARCHAR) AS ISSUE_ID , 
	points.StoryPoints, 
	Sprint.SprintName, 
	CONVERT(nvarchar(10), Sprint.SprintStartDate, 101) as SprintStartDate,
	CONVERT(nvarchar(10), Sprint.SprintEndDate, 101) as SprintEndDate,
	CONVERT(nvarchar(10), Sprint.SprintCompleteDate, 101) as SprintCompleteDate,
	i.SUMMARY, 
	i.DESCRIPTION , 
	i.ASSIGNEE, 
	i.REPORTER , 
	pr.pname AS PRIORITY, 
	s.PNAME AS STATUS, 
	pj.PName as Project, 
	pj.[DESCRIPTION] as ProjDesc
FROM JIRAISSUE i JOIN issuetype y ON i.issuetype = y.id 
JOIN issuestatus s ON i.issuestatus = s.id 
JOIN priority pr ON i.priority = pr.ID 
JOIN project pj ON i.PROJECT = pj.ID 
LEFT JOIN (
	SELECT cfv.issue as StoryPointsId, cfv.NUMBERVALUE as StoryPoints FROM customfieldvalue cfv 
	JOIN customfield cf ON cf.cfname='Story Points' AND cfv.customfield = cf.ID
) AS points ON points.StoryPointsId = i.ID
JOIN (
	SELECT 
		cfv.issue as SprintId, 
		sp.NAME as SprintName, 
		(dateadd(ms, sp.START_DATE%(3600*24*1000), dateadd(day, sp.START_DATE/(3600*24*1000), '1970-01-01 00:00:00.0') )) as SprintStartDate,
		(dateadd(ms, sp.END_DATE%(3600*24*1000), dateadd(day, sp.END_DATE/(3600*24*1000), '1970-01-01 00:00:00.0') )) as SprintEndDate,
		(dateadd(ms, sp.COMPLETE_DATE%(3600*24*1000), dateadd(day, sp.COMPLETE_DATE/(3600*24*1000), '1970-01-01 00:00:00.0') )) as SprintCompleteDate
	FROM customfieldvalue cfv 
	JOIN customfield cf ON cf.cfname='Sprint' AND cfv.customfield = cf.ID
	JOIN AO_60DB71_SPRINT sp ON sp.ID = cfv.STRINGVALUE
) AS Sprint ON Sprint.SprintId = i.ID
AND Sprint.SprintStartDate BETWEEN '01/01/2015' AND '08/19/2015'