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

[Søren Skovsen]

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

[Gregory Van Den Ham]

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. 

[Søren Skovsen]

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