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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.