Find who updated an issue recently in a project

Still need help?

The Atlassian Community is here for you.

Ask the community



Find the details on what was the recently updated issue for a project and who updated it along with the project, issue and user details.

Postgres
WITH jis AS (
select jira.id as id, jira.issuenum as issuenum, jira.project as project, jira.creator as creator, jira.created as created, jira.updated as updated 
from (select ji.id as id, ji.issuenum as issuenum, ji.project as project, ji.creator as creator, ji.created as created, ji.updated as updated, row_number() over (partition by project order by updated desc) as rno 
from jiraissue ji)jira where jira.rno = 1
 )
select final.project_id, final.Project_Name,final.Project_Type,final.project_key,final.Project_Lead,final.projectlead_email_address, final.project_key||'-'||final.issuenum as last_issue_tobe_updated,final.last_user_to_update,cus.email_address as last_user_toupdate_email,final.last_updated_time,final.Number_of_Issues
from (select tbltwo.* from(select tblone.*, row_number() over (partition by last_issue_tobe_updated order by last_updated_time desc) as ranknum
from (select p.id as project_id, p.pname as Project_Name, p.pkey as project_key,jis.id as last_issue_tobe_updated, jis.issuenum as issuenum, jis.creator as last_user_to_update, jis.created as last_updated_time, p.Projecttype as Project_Type, p.pcounter as Number_of_Issues, p.lead as Project_Lead,cu.email_address as projectlead_email_address,jis.updated as Last_issue_update from Project p
join jis on jis.project = p.id
join app_user au on au.user_key = p.lead
join cwd_user cu on cu.lower_user_name = au.lower_user_name
union
select p.id as project_id, p.pname as Project_Name, p.pkey as project_key, jis.id as last_issue_tobe_updated, jis.issuenum as issuenum, jaction.author as last_user_to_update, jaction.comment_updated_time as last_updated_time,p.Projecttype as Project_Type, p.pcounter as Number_of_Issues, p.lead as Project_Lead,cu.email_address as projectlead_email,jis.updated as Last_issue_update
from Project p
join jis on jis.project = p.id
join app_user au on au.user_key = p.lead
join cwd_user cu on cu.lower_user_name = au.lower_user_name
join (select jact.author as author,jact.issueid as issueid, jact.updated as comment_updated_time from (select ja.author as author,ja.issueid as issueid, ja.updated, row_number() over (partition by issueid order by updated desc) as rno from jiraaction ja) jact where jact.rno = 1)jaction on jaction.issueid = jis.id
union
select p.id as project_id, p.pname as Project_Name, p.pkey as project_key, jis.id as last_issue_tobe_updated, jis.issuenum as issuenum, cgroups.author as last_user_to_update, cgroups.created as last_updated_time, p.Projecttype as Project_Type, p.pcounter as Number_of_Issues, p.lead as Project_Lead,cu.email_address as projectlead_email,jis.updated as Last_issue_update
from Project p
join jis on jis.project = p.id
join app_user au on au.user_key = p.lead
join cwd_user cu on cu.lower_user_name = au.lower_user_name
join (select cgroup.author as author,cgroup.issueid as issueid, cgroup.created as created from (select cg.author as author,cg.issueid as issueid, cg.created as created, row_number() over (partition by issueid order by created desc) as rno from changegroup cg) cgroup where cgroup.rno = 1)cgroups on cgroups.issueid = jis.id order by last_issue_tobe_updated)tblone)tbltwo where tbltwo.ranknum = 1)final join cwd_user cus on cus.user_name = final.last_user_to_update order by final.Number_of_Issues desc;
DescriptionJira SQL Reporting
ProductJira






Last modified on Nov 23, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.