Grupy – lista osób w grupie<

select * from cwd_user where lower_user_name in (select child_name from cwd_membership where parent_name = 'jira-administrators')

Zgłoszenia w konkretnym projekcie

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,
ji.pkey AS issue_id, ji.reporter AS issue_reporter, pri.pname AS issue_priority, ist.pname AS issue_status, ji.summary AS issue_summary
FROM project p LEFT OUTER JOIN jiraissue ji ON ji.project = p.id
LEFT OUTER JOIN priority pri ON ji.priority = pri.id
LEFT OUTER JOIN issuestatus ist ON ji.issuestatus = ist.id
WHERE p.pname = ''
ORDER BY ji.pkey;

Komponenty w projekcie

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,
c.cname AS component_name, c.url As component_url, c.lead AS component_lead, c.assigneetype AS component_assignee_type
FROM project p LEFT OUTER JOIN component c ON c.project = p.id
WHERE p.pname = '';

Role w projekcie

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead, prc.roletypeparameter AS project_roles
FROM project p LEFT OUTER JOIN projectroleactor prc ON prc.pid = p.id
WHERE p.pname = '';

Załączniki – suma dla projektów ze znacznikiem CLOSED

SELECT p.pname, sum(f.FILESIZE)
FROM jira7db.project p, jira7db.jiraissue i, jira7db.fileattachment f
where p.id = i.project and
i.id = f.issueid
and p.pname like "%CLOSED%"
GROUP BY p.pname;

Issue ID, Nazwa projektu, Numer zgłoszenia, Summary, Typ zadania, Login AD, timeworked

select issueid, PR.pname as ProjectName, CONCAT(PR.pkey, "-", JI.issuenum) as NumerZgloszenia, JI.SUMMARY as NazwaZadania, IT.pname as IssueType, AUTHOR as LoginAD, timeworked
from worklog WL join jiraissue JI on WL.issueid = JI.ID join project PR on JI.project = PR.ID join issuetype IT on IT.ID = JI.issuetype

Issue ID, component

SELECT ji.id, c.cname FROM jiraissue ji 
INNER JOIN nodeassociation na ON ji.id = na.source_node_id
INNER JOIN component c ON na.sink_node_id = c.id

Issue ID, Epic name

SELECT issue, STRINGVALUE as Epic_Name FROM customfieldvalue
where CUSTOMFIELD = 10334

Wyszukanie używanych customfield

SELECT CF.cfname, count(CV.ISSUE) FROM customfield CF join customfieldvalue CV on CF.ID = CV.CUSTOMFIELD
group by CF.cfname