Hi All,
Greetings!
I have been looking to find an SQL that can provide the list of all projects which list all the projects using a specific issue type.
I found this thread List of project using a particular issue type.
However, the issue is that it does not list the projects that use the "Default Issue type Scheme" as i see no entries for them in the "OPTIONCONFIGURATION" table.
Can anyone help on this issue?
Thanks,P
Hi, Joshi
This query will return all projects that use the specified issue type.
select IT.id issue_id, IT.pname issuetype, PROJ.pname project_name,CC.customfieldfrom configurationcontext CCjoin optionconfiguration OC on OC.fieldconfig = CC.fieldconfigschemejoin issuetype IT on IT.id = OC.optionidjoin project PROJ on PROJ.id = CC.projectwhere lower(IT.pname) like '%epic%' --specify the issue type you wantorder by IT.pname;
Thank you very much for the answer and first response.
What i noticed was that this query returns fewer projects than the other query.We are expecting about 800+ projects where my query returns 500+ projects and your's returns about 300.
So something is missed and yet to look at DB design for it.we are also working with support and searching atlassian KB to get any info.
My original query is
SELECT DISTINCT P.PKEY as proj_keyFROM JIRA.PROJECT P JOIN JIRA.CONFIGURATIONCONTEXT CC ON P.ID = CC.PROJECTJOIN JIRA.FIELDCONFIGSCHEME FCS ON FCS.ID = CC.FIELDCONFIGSCHEMEJOIN JIRA.FIELDCONFIGSCHEMEISSUETYPE FCI ON FCI.FIELDCONFIGSCHEME = CC.FIELDCONFIGSCHEMEJOIN JIRA.OPTIONCONFIGURATION OC ON OC.FIELDCONFIG = FCI.FIELDCONFIGURATIONJOIN JIRA.ISSUETYPE IT ON IT.ID = OC.OPTIONIDwhere (IT.PNAME = 'XYZ' or IT.PNAME = 'ABC')order by proj_key;
Yes, inded. I can offer you a request that will return all projects of the specified type, but only under the condition that there is at least one task according to your type.
select distinct ji.project project_id,pr.pname project_name,it.pname issue_type from jiraissue jijoin issuetype it on it.id=ji.issuetypejoin project pr on pr.id=ji.projectwhere it.pname='Epic';
Finally, this SQL works perfect for obtain all issuetype for each project.
select PROJ.pname project_name,PROJ.pkey,IT.id issue_id, IT.pname issuetype,CC.customfieldfrom configurationcontext CCjoin optionconfiguration OC on OC.fieldconfig = CC.fieldconfigschemejoin issuetype IT on IT.id = OC.optionidjoin project PROJ on PROJ.id = CC.projectwhere CC.customfield ='issuetype'order by PROJ.pname,IT.pname
It looks like you're new here. Sign in or register to get started.
