SQL: Finding story or epic for issue

[Jon Watte]

I'm querying the database (MySQL) using raw SQL. I want a report that is "time logged against each story/epic in date range"

I can get "time logged against each issue in date range" by joining worklog and jiraissue.

However, I don't know how to map an issueid to a story or epic. Where in the database is this stored, and how do I get it back?

[JamieA · Accepted solution]

You need to join jiraissue to IssueLink, then back to jiraissue, on source and destination to jiraissue.id.

I find that these queries quickly get out of hand, and it's probably worth writing a plugin. That way you can also give your users access to the data without letting them query the db directly.

[Jon Watte]

This works. What I ended up with was:

select sum(w.timeworked)/3600, j.id, j.summary from worklog w, jiraissue i, jiraissue j, issuelink l where w.issueid = i.id and i.project=10060 and w.updated >= '2012-01-01' and i.id = l.destination and j.id = l.source group by j.id;

With "10060" and "10000" being installation specific.