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 = 'TDS3SBM'
--And issuetype.pname = 'Bug'
--and Resolution is null
order by jiraissue.issuenum
|