Page History

SELECT issuenum, issuestatus.pname, reporter, assignee, issuetype, summary, customfieldvalue.stringvalue as Broker ID
   FROM jiraissue, issuestatus
   WHERE project = (select id from project where pkey='SBM')
     AND issuestatus.id = jiraissue.issuestatus 
   ORDER BY issuenum;

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 = 'SBM'
 --And issuetype.pname = 'Bug'
 --and Resolution is null

 order by jiraissue.issuenum