SQL Server: Query all issues for a specific project and the linked fix versions

Question

I like to to have a MS SQL query that can list all issues and there fix versions for a specific project.

How can I do that?

/Soren

Søren Skovsen · Answer

Hi, I like to have it using SQL as I am going to do a dynamic query in Excel.

I have the following SQL, but it does not contain the fix version, and I need that to do the Excel calculations.

select

project.pname as ProjectName
 ,project.pkey as ProjectKey
 ,jiraissue.issuenum as IssueNumber
 ,issuetype.pname as Type
 ,priority.pname as Priority
 ,project.projecttype as ListType
 ,customfieldvalue.stringvalue as Sponsor
 ,ReqIDNest.STRINGVALUE as RequirementID
 --,label.LABEL
 ,ComNests.cname
 ,issuestatus.pname as Status
 ,resolution.pname as Resolution
 ,jiraissue.ASSIGNEE
 ,jiraissue.REPORTER
 ,jiraissue.CREATED
 ,jiraissue.DUEDATE
 ,jiraissue.TIMEORIGINALESTIMATE/3600 as OriginalEstimate
 ,jiraissue.TIMEESTIMATE/3600 as RemainingEstimate
 ,jiraissue.TIMESPENT/3600 as HoursSpent

from jiraissue

left join project on --project name, list type
jiraissue.PROJECT = project.ID

left join issuetype on --type
jiraissue.issuetype = issuetype.ID

left join priority on -- Priority
jiraissue.PRIORITY = priority.ID

--left join label on --to look for individual labes, can create duplicate issues.
--jiraissue.id = label.ISSUE
--and LABEL.LABEL in ('Li1', 'Li2','Li3', 'Li4')

left join customfieldvalue on --sponsor
jiraissue.ID = customfieldvalue.ISSUE
and customfieldvalue.CUSTOMFIELD = '10001'

left join (select * from customfieldvalue) as ReqIDNest on --Requirement ID
jiraissue.ID = ReqIDNest.ISSUE
and ReqIDNest.CUSTOMFIELD = '10700'

left join resolution on --resolution
jiraissue.RESOLUTION = resolution.ID

----Return for link if nessesary

left join issuestatus on --status
jiraissue.issuestatus = issuestatus.ID

left join ---- adding component, but should be specific otherwise it will count double.
 (SELECT jiraissue.id, component.cname FROM nodeassociation, component, jiraissue, project 
 WHERE project.ID = jiraissue.PROJECT and component.ID = nodeassociation.SINK_NODE_ID 
 AND jiraissue.id = nodeassociation.SOURCE_NODE_ID AND
 nodeassociation.ASSOCIATION_TYPE = 'IssueComponent') As ComNests

on jiraissue.ID = ComNests.ID
 and cname like '%SW:%'

where project.pkey = 'TDS3'
 --And issuetype.pname = 'Bug'
 --and Resolution is null

order by jiraissue.issuenum

Gregory Van Den Ham_2hc9n5 · Answer

A SQL query or JQL Query from within Jira?

An easy JQL is:

Project = "whatever" AND fixVersion is not EMPTY

You can export that to csv to manipulate.   I'm always hesitant to touch backend SQL without getting a full use case explained first.