Measuring Story Movement

Question

Does anyone have a query that runs against the Jira database that would count the number of hops a story took (back and forth) between columns in Greenhopper? I'd like to measure average story movement left-to-right and right-to-left.

Thanks

Matthew Zach · Accepted Answer

This got me what I needed:

select ji.pkey as "Story", count(*) as "# of hops"

from changeitem ci join changegroup cg on ci.groupid=cg.id

join jiraissue ji on ji.id=cg.issueid

where ci.field='status'

and ji.project = XXXXX

group by ji.pkey;