SQL for JIRA

Get all issues in project DEMO

select ji.*
from project p
join jiraissue ji on (ji.project = p.id)
where p.pkey = ‘DEMO’


Get total efforts in project DEMO

SELECT sum(ji.timespent/3600) wl_total
from project p
join jiraissue ji on (ji.project = p.id)
where p.pkey = ‘DEMO’


Get list of all closed bugs in project DEMO

SELECT ji.*
from project p
join jiraissue ji on (ji.project = p.id)
join issuetype it on (it.id = ji.issuetype)
join issuestatus ist on (ist.id = ji.issuestatus)
where ji.project = p.id
and p.pkey = ‘DEMO’
and it.pname in (‘Bug’)
and ist.pname = ‘Closed’


Get count of Reopened issues in project DEMO

SELECT ji.pkey, ji. summary, count(*)
FROM changeitem ci
join changegroup cg on (cg.id = ci.groupid)
join jiraissue ji on (cg.issueid = ji.id)
join project p on (p.id = ji.project)
where field = ‘status’
and cast(ci.newstring as nvarchar) = ‘Reopened’
and p.pkey = ‘DEMO’
group by ji.pkey, ji. summary