Use case: Sprint predictability and spillover

This SQL export answers “how predictable is this team?” for a board’s last five closed sprints. For each sprint it reports:

• how many stories the team committed to;
• how many of those were done when the sprint closed;
• how many were added after the sprint started;
• how many spilled over into a later sprint.

Jira’s Sprint field only shows which sprint a work item is in now, plus the sprints it has been in. To know what was in a sprint when it started, the query reads each work item’s sprint history.

Set up the export

1. Create an export whose work items come from the team’s board, so the report covers only that board’s sprints. A story that was once in another board’s sprint brings that sprint along; if one shows up in the result, add AND name LIKE 'Team A%' (your naming) to the closed step.
2. Add the Sprint field. Add Story points too if you want points rather than a count (see Adjusting the query).
3. In Settings, turn on Export historical changes. Without the history, the query cannot tell what was in a sprint at its start.
4. Under Output, choose SQL query and paste the query below.

What the numbers mean

Column	Meaning
committed	Stories in the sprint when it started
committed_done	Committed stories in a Done-category status when the sprint closed
added_after_start	Stories that joined the sprint after it started and were still in it at its close
spilled_over	Stories in the sprint at its close that were not done: they carry over
predictability_pct	committed_done ÷ committed
spillover_pct	spilled_over ÷ every story in the sprint at its close

A committed story that was removed from the sprint before it closed counts as committed but not done: it lowers predictability, but it is not spillover.

The query

-- Predictability of story completion, last 5 closed sprints
WITH closed AS (
  SELECT id, name, startDate, completeDate
  FROM sprints
  WHERE state = 'closed'
  ORDER BY completeDate DESC
  LIMIT 5
),
sprint_field AS (
  SELECT id FROM fields
  WHERE json_extract_string(schema, '$.custom') = 'com.pyxis.greenhopper.jira:gh-sprint'
),
membership AS (   -- was the story in the sprint at its start, and at its close?
  SELECT s.id AS sprint_id, f.issue_id,
         bool_or(coalesce(f.started_at, '-infinity') <= s.startDate
                 AND (f.ended_at IS NULL OR f.ended_at > s.startDate)) AS committed,
         bool_or(coalesce(f.started_at, '-infinity') <= s.completeDate
                 AND (f.ended_at IS NULL OR f.ended_at >= s.completeDate)) AS at_close
  FROM closed s
  JOIN field_intervals f ON f.value_id = s.id AND f.field_id IN (SELECT id FROM sprint_field)
  JOIN work_items w ON w.id = f.issue_id
  JOIN issuetypes t ON t.id = w.issuetype AND t.name = 'Story'
  GROUP BY 1, 2
),
done_at_close AS (   -- in a Done-category status when the sprint closed
  SELECT DISTINCT m.sprint_id, m.issue_id
  FROM membership m
  JOIN closed s ON s.id = m.sprint_id
  JOIN status_intervals si ON si.issue_id = m.issue_id AND si.category_key = 'done'
   AND si.started_at <= s.completeDate
   AND (si.ended_at IS NULL OR si.ended_at > s.completeDate)
),
per_sprint AS (
  SELECT s.name AS sprint, s.completeDate::DATE AS closed_on,
         count(*) FILTER (WHERE m.committed) AS committed,
         count(*) FILTER (WHERE m.committed AND d.issue_id IS NOT NULL) AS committed_done,
         count(*) FILTER (WHERE NOT m.committed AND m.at_close) AS added_after_start,
         count(*) FILTER (WHERE m.at_close AND d.issue_id IS NULL) AS spilled_over,
         count(*) FILTER (WHERE m.at_close) AS in_sprint_at_close
  FROM closed s
  JOIN membership m ON m.sprint_id = s.id
  LEFT JOIN done_at_close d ON d.sprint_id = m.sprint_id AND d.issue_id = m.issue_id
  GROUP BY 1, 2
)
SELECT sprint, closed_on, committed, committed_done, added_after_start, spilled_over,
       round(100.0 * committed_done / nullif(committed, 0)) AS predictability_pct,
       round(100.0 * spilled_over / nullif(in_sprint_at_close, 0)) AS spillover_pct
FROM per_sprint
ORDER BY closed_on

How it works:

• closed picks the five most recently closed sprints.
• sprint_field finds the Sprint field by its type, so the query works whatever the field’s id is on your site.
• membership reads each story’s periods in each sprint from field_intervals. A story can leave a sprint and come back, so bool_or asks whether any of its periods covered the start, and any covered the close.
• done_at_close reads status_intervals for the status each story was in when the sprint closed.

Example result

The numbers below are made up, to show the shape of the result.

sprint	closed_on	committed	committed_done	added_after_start	spilled_over	predictability_pct	spillover_pct
Sprint 38	2026-06-26	18	14	3	5	78	24
Sprint 39	2026-07-10	20	17	2	4	85	18
Sprint 40	2026-07-24	22	15	4	9	68	35
Sprint 41	2026-08-07	19	16	1	3	84	15
Sprint 42	2026-08-21	21	18	2	4	86	17

Adjusting the query

• Other work item types. Change t.name = 'Story' to the type names your team uses, for example t.name IN ('Story', 'Bug'), or remove the condition to count everything except sub-tasks with t.hierarchyLevel = 0.
• More or fewer sprints. Change LIMIT 5.
• Story points instead of a count. Add the points field to membership, for example any_value(w.customfield_10016) AS points, and in per_sprint replace each count(*) FILTER (...) with coalesce(sum(m.points) FILTER (...), 0). Your site’s field id may differ: the field picker shows it beside the field’s name.
• “Done” means the Done status category. Jira’s Sprint Report counts a work item as completed when it is in the board’s last column. If your board’s last column holds a status that is not in the Done category, the two can differ.

Checking it against Jira

Before you share the numbers, compare one sprint with Jira’s own reports:

• the Sprint Report lists the work items completed and not completed, and those added after the sprint started;
• the Velocity Report shows commitment and completed per sprint, in the board’s estimation statistic (often story points rather than a count).

A difference of a story or two usually comes from the definition of done above, or from a story that was removed from the sprint before it closed.

Schedule it

Save the export and schedule it to run every week, every two weeks to match your sprints, or monthly, as Excel or CSV. Each run reads fresh data from Jira, so the five sprints move along as the team closes new ones.

Or ask an AI agent, and skip the SQL

The Rovo agent, or an AI assistant connected through MCP such as Claude, can set all of this up from one message, for example: “for the Team A board, report committed vs done stories and spillover for the last five closed sprints, and email it to me as Excel every other Monday”. The agent loads the board’s work items with their history, writes and runs the query, and shows you the result. Once you confirm, it saves the query as a SQL export and schedules it.