I want a list of jiras created against each issue type under all the project from jan 2024. I am using below sql but it doesn't return list of those issue types where no jira was created from above mentioned date.
SELECT
p.pkey AS project_key,
it.pname AS issue_type,
COUNT(i.id) AS issue_count,
COALESCE(au.lower_user_name, 'Unknown') AS project_lead, -- Project Lead (username from app_user table)
COALESCE(cu.email_address, 'No Email') AS lead_email, -- Email of Project Lead (from cwd_user table)
COALESCE(pc.cname, 'No Category') AS project_category, -- Project Category
MAX(i.created) AS last_issue_created_date -- Last Issue Creation Date
FROM
project p
JOIN
jiraissue i ON i.project = p.id
JOIN
issuetype it ON i.issuetype = it.id
LEFT JOIN
app_user au ON p.LEAD = au.user_key -- Fetch project lead username
LEFT JOIN
cwd_user cu ON au.lower_user_name = cu.lower_user_name -- Fetch project lead email
LEFT JOIN
nodeassociation na_pc ON na_pc.source_node_id = p.id
AND na_pc.association_type = 'ProjectCategory'
AND na_pc.sink_node_entity = 'ProjectCategory' -- Link project to category
LEFT JOIN
projectcategory pc ON na_pc.sink_node_id = pc.id -- Fetch project category name
WHERE
p.pkey NOT LIKE 'Z-%'
AND p.pkey NOT LIKE 'z-%'
AND p.pkey NOT LIKE 'z - %'
AND p.pkey NOT LIKE 'Z - %'
AND p.pkey NOT IN ('BCS', 'DEVO', 'MCLS', 'SIOP')
AND i.created >= '2024-01-01'
GROUP BY
p.pkey, it.pname, au.lower_user_name, cu.email_address, pc.cname, p.id
ORDER BY
p.id, it.pname -- Sorting by project ID, then by issue type;
Hi @Hussein s -- Welcome to the Atlassian Community!
Jira Query Language (JQL) is not an SQL, and does not have many of the SQL features you are trying:
To learn what is supported, please see this documentation: https://support.atlassian.com/jira-software-cloud/docs/use-advanced-search-with-jira-query-language-jql/
When you need additional query features, please investigate marketplace addons / apps, or export / pull the issues into another database tool which supports SQL.
Kind regards,
Bill
Hi Bill, I am using Jira DC & running sql in db. I am almost there except the thing which I have mentioned in question.
Not using jql as we have so many things restrictions such as security level & hence sql to get accurate count.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
My mistake; your question is indicated as "Cloud" which has no direct access to the database for such queries.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Spend the day sharpening your skills in Atlassian Cloud Organization Admin or Jira Administration, then take the exam onsite. Already ready? Take one - or more - of 12 different certification exams while you’re in Anaheim at Team' 25.
Learn more
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.