WITH jis AS (
	select p.id as project_id, 
			p.pname as Project_Name, 
			p.Projecttype as ProjectType,
			p.pkey as project_key, 
			jira.id as last_issue_tobe_updated, 
			jira.issuenum as issuenum,
			p.Projecttype as Project_Type, 
			p.pcounter as Number_of_Issues, 
			p.lead as Project_Lead,
			cu.email_address as projectlead_email,
			jira.creator as creator,
			Issuecounttbl.Issuecount as Issuecount,
			jira.updated as Last_issue_update,
			jira.created as Jira_created_date
	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 
	join (select project,
				count(*) as Issuecount 
			from jiraissue 
			group by project) Issuecounttbl on jira.project = Issuecounttbl.project 
	join Project p on jira.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 
	where jira.rno = 1)
select final.Issueid, 
		final.Project_Name,
		final.ProjectType,
		final.project_key,
		final.Project_Lead,
		final.projectlead_email_address, 
		concat(final.project_key,'-',final.issuenum) as last_issue_tobe_updated,
		final.last_user_to_update,
		final.last_updated_time,
		final.Number_of_Issues, 
		final.Last_update_Action
from (select tbltwo.* 
		from (select tblone.*, 
					row_number() over (partition by last_issue_tobe_updated order by last_updated_time desc) as ranknum
				from
					--Find the latest issue update from jiraissue if the issue was just created
					(select jis.last_issue_tobe_updated as Issueid, 
							jis.Project_Name as Project_Name,
							jis.ProjectType as ProjectType,
							jis.project_key as project_key,
							jis.last_issue_tobe_updated as last_issue_tobe_updated, 
							jis.issuenum as issuenum, 
							jis.creator as last_user_to_update, 
							jis.Jira_created_date as last_updated_time, 
							jis.Issuecount as Number_of_Issues, 
							jis.Project_Lead as Project_Lead,
							jis.projectlead_email as projectlead_email_address,
							jis.Last_issue_update as Last_issue_update, 
							'Issue Creation' as Last_update_Action
					from jis 
union
	--Find the latest issue update from jiraaction if the issue had comments
	select jis.last_issue_tobe_updated as Issueid, 
			jis.Project_Name as Project_Name,
			jis.ProjectType as ProjectType,
			jis.project_key as project_key,
			jis.last_issue_tobe_updated as last_issue_tobe_updated, 
			jis.issuenum as issuenum, 
			jaction.author as last_user_to_update, 
			jaction.comment_updated_time as last_updated_time, 
			jis.Issuecount as Number_of_Issues, 
			jis.Project_Lead as Project_Lead,
			jis.projectlead_email as projectlead_email_address,
			jis.Last_issue_update as Last_issue_update, 
			'Comment Addition' as Last_update_Action
	from jis 
	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.last_issue_tobe_updated 
union
--Find the latest issue update from worklog if the last action on the table was adding worklog
	select jis.last_issue_tobe_updated as Issueid, 
			jis.Project_Name as Project_Name,
			jis.ProjectType as ProjectType,
			jis.project_key as project_key,
			jis.last_issue_tobe_updated as last_issue_tobe_updated, 
			jis.issuenum as issuenum, 
			wrklog.author as last_user_to_update, 
			wrklog.comment_updated_time as last_updated_time, 
			jis.Issuecount as Number_of_Issues, 
			jis.Project_Lead as Project_Lead,
			jis.projectlead_email as projectlead_email_address,
			jis.Last_issue_update as Last_issue_update, 
			'Worklog Addition' as Last_update_Action
	from jis 
	join (select wlog.author as author,
				wlog.issueid as issueid, 
				wlog.updated as comment_updated_time 
			from (select wl.updateauthor as author,
						wl.issueid as issueid, 
						wl.updated, row_number() over (partition by issueid order by updated desc) as rno 
					from worklog wl) wlog 
			where wlog.rno = 1) wrklog on wrklog.issueid = jis.last_issue_tobe_updated 
union
	--Find the latest change history from change group from either from comment delete or issue transition or any other such activity.
	select jis.last_issue_tobe_updated as Issueid, 
			jis.Project_Name as Project_Name,
			jis.ProjectType as ProjectType,
			jis.project_key as project_key,
			jis.last_issue_tobe_updated as last_issue_tobe_updated, 
			jis.issuenum as issuenum, 
			cgroups.author as last_user_to_update, 
			cgroups.created as last_updated_time, 
			jis.Issuecount as Number_of_Issues, 
			jis.Project_Lead as Project_Lead,
			jis.projectlead_email as projectlead_email_address,
			jis.Last_issue_update as Last_issue_update, 
			'Change History Action' as Last_update_Action
	from jis 
	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 join changeitem ci on ci.id=cg.id and ci.field not in ('WorklogId','timeestimate','timespent')) cgroup
			where cgroup.rno = 1) cgroups on cgroups.issueid = jis.last_issue_tobe_updated 
	order by last_issue_tobe_updated) tblone) tbltwo 
where tbltwo.ranknum = 1) final 
order by final.Number_of_Issues desc;