query generated from new summary footer

sql.sql

select
	'' as "entityId",
	'' as "rowTagTeamDeactivated",
	'' as "entityName",
	'' as "dp-val-0",
	sum("dp-val-1") as "dp-val-1",
	sum("dp-val-2") as "dp-val-2",
	(sum("cal-dp-val-3-0-0") / nullif(sum("cal-dp-val-3-0-1"), 0)) * 100 as "dp-val-3",
	sum("cal-dp-val-4-0") / nullif(sum("cal-dp-val-4-1"), 0) as "dp-val-4",
	sum("cal-dp-val-5-0") + sum("cal-dp-val-5-1") + sum("cal-dp-val-5-2") as "dp-val-5"
from
	(
	select
		"child_team"."id" as "entityId",
		not child_team.is_active as "rowTagTeamDeactivated",
		"child_team"."name" as "entityName",
		child_team.name as "dp-val-0",
		"dp-val-1",
		"dp-val-2",
		"cal-dp-val-3-0-0",
		"cal-dp-val-3-0-1",
		"dp-val-3",
		"cal-dp-val-4-0",
		"cal-dp-val-4-1",
		"dp-val-4",
		"cal-dp-val-5-0",
		"cal-dp-val-5-1",
		"cal-dp-val-5-2",
		"dp-val-5"
	from
		"teams" as "child_team"
	left join "team_hierarchy" as "hierarchy" on
		(("hierarchy"."child_team_id" = "child_team"."id"))
	left join "teams" as "team" on
		(("hierarchy"."parent_team_id" = "team"."id"))
	left join (
		select
			sum(case when instance.status = 'Completed' then 1 else 0 end)::decimal as "dp-val-1",
			sum(case when instance.status = 'InProgress' then 1 else 0 end)::decimal as "dp-val-2",
			"child_team"."id" as "entityId"
		from
			"teams" as "child_team"
		left join "team_hierarchy" as "hierarchy" on
			(("hierarchy"."child_team_id" = "child_team"."id"))
		left join "teams" as "team" on
			(("hierarchy"."parent_team_id" = "team"."id"))
		inner join "form_instance_teams" as "instance_team" on
			(("child_team"."id" = "instance_team"."team_id"
			and instance_team.source in ('Subject',
			'ParentTeam')
			and (instance_team.role in ('Member',
			'Manager')
			or instance_team.role is null)))
		inner join "form_instances" as "instance" on
			(("instance"."id" = "instance_team"."instance_id"))
		where
			not ("child_team"."type" = 'Role')
			and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
			and "instance"."tenant" = 'Marshall'
			and "instance"."module" = 'Coaching'
			and "instance"."created_at" >= '2019-06-01'
			and "instance"."created_at" <= '2020-05-14 23:59:59'
		group by
			"entityId") as "subQuery2" on
		(("subQuery2"."entityId" = "child_team"."id"))
	left join (
		select
			"child_team"."id" as "entityId",
			"cal-dp-val-3-0-0" as "cal-dp-val-3-0-0",
			"cal-dp-val-3-0-1" as "cal-dp-val-3-0-1",
			"cal-dp-val-3-0" * 100 as "dp-val-3",
			"cal-dp-val-3-0"
		from
			"teams" as "child_team"
		left join "team_hierarchy" as "hierarchy" on
			(("hierarchy"."child_team_id" = "child_team"."id"))
		left join "teams" as "team" on
			(("hierarchy"."parent_team_id" = "team"."id"))
		left join (
			select
				"child_team"."id" as "entityId",
				"cal-dp-val-3-0-0" as "cal-dp-val-3-0-0",
				"cal-dp-val-3-0-1" as "cal-dp-val-3-0-1",
				"cal-dp-val-3-0-0" / nullif("cal-dp-val-3-0-1", 0) as "cal-dp-val-3-0"
			from
				"teams" as "child_team"
			left join "team_hierarchy" as "hierarchy" on
				(("hierarchy"."child_team_id" = "child_team"."id"))
			left join "teams" as "team" on
				(("hierarchy"."parent_team_id" = "team"."id"))
			left join (
				select
					sum(case when instance.status = 'Completed' then 1 else 0 end)::decimal as "cal-dp-val-3-0-0",
					"child_team"."id" as "entityId"
				from
					"teams" as "child_team"
				left join "team_hierarchy" as "hierarchy" on
					(("hierarchy"."child_team_id" = "child_team"."id"))
				left join "teams" as "team" on
					(("hierarchy"."parent_team_id" = "team"."id"))
				inner join "form_instance_teams" as "instance_team" on
					(("child_team"."id" = "instance_team"."team_id"
					and instance_team.source in ('Subject',
					'ParentTeam')
					and (instance_team.role in ('Member',
					'Manager')
					or instance_team.role is null)))
				inner join "form_instances" as "instance" on
					(("instance"."id" = "instance_team"."instance_id"))
				where
					not ("child_team"."type" = 'Role')
					and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
					and "instance"."tenant" = 'Marshall'
					and "instance"."module" = 'Coaching'
					and "instance"."created_at" >= '2019-06-01'
					and "instance"."created_at" <= '2020-05-14 23:59:59'
				group by
					"entityId") as "subQuery2" on
				(("subQuery2"."entityId" = "child_team"."id"))
			left join (
				select
					sum(case when instance.status = 'InProgress' then 1 else 0 end)::decimal as "cal-dp-val-3-0-1",
					"child_team"."id" as "entityId"
				from
					"teams" as "child_team"
				left join "team_hierarchy" as "hierarchy" on
					(("hierarchy"."child_team_id" = "child_team"."id"))
				left join "teams" as "team" on
					(("hierarchy"."parent_team_id" = "team"."id"))
				inner join "form_instance_teams" as "instance_team" on
					(("child_team"."id" = "instance_team"."team_id"
					and instance_team.source in ('Subject',
					'ParentTeam')
					and (instance_team.role in ('Member',
					'Manager')
					or instance_team.role is null)))
				inner join "form_instances" as "instance" on
					(("instance"."id" = "instance_team"."instance_id"))
				where
					not ("child_team"."type" = 'Role')
					and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
					and "instance"."tenant" = 'Marshall'
					and "instance"."module" = 'Coaching'
					and "instance"."created_at" >= '2019-06-01'
					and "instance"."created_at" <= '2020-05-14 23:59:59'
				group by
					"entityId") as "subQuery3" on
				(("subQuery3"."entityId" = "child_team"."id"))
			where
				not ("child_team"."type" = 'Role')
				and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46') as "subQuery2" on
			(("subQuery2"."entityId" = "child_team"."id"))
		where
			not ("child_team"."type" = 'Role')
			and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46') as "subQuery3" on
		(("subQuery3"."entityId" = "child_team"."id"))
	left join (
		select
			"child_team"."id" as "entityId",
			"cal-dp-val-4-0" as "cal-dp-val-4-0",
			"cal-dp-val-4-1" as "cal-dp-val-4-1",
			"cal-dp-val-4-0" / nullif("cal-dp-val-4-1", 0) as "dp-val-4"
		from
			"teams" as "child_team"
		left join "team_hierarchy" as "hierarchy" on
			(("hierarchy"."child_team_id" = "child_team"."id"))
		left join "teams" as "team" on
			(("hierarchy"."parent_team_id" = "team"."id"))
		left join (
			select
				sum(case when instance.status = 'InProgress' then 1 else 0 end)::decimal as "cal-dp-val-4-0",
				"child_team"."id" as "entityId"
			from
				"teams" as "child_team"
			left join "team_hierarchy" as "hierarchy" on
				(("hierarchy"."child_team_id" = "child_team"."id"))
			left join "teams" as "team" on
				(("hierarchy"."parent_team_id" = "team"."id"))
			inner join "form_instance_teams" as "instance_team" on
				(("child_team"."id" = "instance_team"."team_id"
				and instance_team.source in ('Subject',
				'ParentTeam')
				and (instance_team.role in ('Member',
				'Manager')
				or instance_team.role is null)))
			inner join "form_instances" as "instance" on
				(("instance"."id" = "instance_team"."instance_id"))
			where
				not ("child_team"."type" = 'Role')
				and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
				and "instance"."tenant" = 'Marshall'
				and "instance"."module" = 'Coaching'
				and "instance"."created_at" >= '2019-06-01'
				and "instance"."created_at" <= '2020-05-14 23:59:59'
			group by
				"entityId") as "subQuery2" on
			(("subQuery2"."entityId" = "child_team"."id"))
		left join (
			select
				count(instance.id)::decimal as "cal-dp-val-4-1",
				"child_team"."id" as "entityId"
			from
				"teams" as "child_team"
			left join "team_hierarchy" as "hierarchy" on
				(("hierarchy"."child_team_id" = "child_team"."id"))
			left join "teams" as "team" on
				(("hierarchy"."parent_team_id" = "team"."id"))
			inner join "form_instance_teams" as "instance_team" on
				(("child_team"."id" = "instance_team"."team_id"
				and instance_team.source in ('Subject',
				'ParentTeam')
				and (instance_team.role in ('Member',
				'Manager')
				or instance_team.role is null)))
			inner join "form_instances" as "instance" on
				(("instance"."id" = "instance_team"."instance_id"))
			where
				not ("child_team"."type" = 'Role')
				and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
				and "instance"."tenant" = 'Marshall'
				and "instance"."module" = 'Coaching'
				and "instance"."created_at" >= '2019-06-01'
				and "instance"."created_at" <= '2020-05-14 23:59:59'
			group by
				"entityId") as "subQuery3" on
			(("subQuery3"."entityId" = "child_team"."id"))
		where
			not ("child_team"."type" = 'Role')
			and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46') as "subQuery4" on
		(("subQuery4"."entityId" = "child_team"."id"))
	left join (
		select
			"child_team"."id" as "entityId",
			"cal-dp-val-5-0" as "cal-dp-val-5-0",
			"cal-dp-val-5-1" as "cal-dp-val-5-1",
			"cal-dp-val-5-2" as "cal-dp-val-5-2",
			"cal-dp-val-5-0" + "cal-dp-val-5-1" + "cal-dp-val-5-2" as "dp-val-5"
		from
			"teams" as "child_team"
		left join "team_hierarchy" as "hierarchy" on
			(("hierarchy"."child_team_id" = "child_team"."id"))
		left join "teams" as "team" on
			(("hierarchy"."parent_team_id" = "team"."id"))
		left join (
			select
				sum(case when instance.status = 'InProgress' then 1 else 0 end)::decimal as "cal-dp-val-5-0",
				"child_team"."id" as "entityId"
			from
				"teams" as "child_team"
			left join "team_hierarchy" as "hierarchy" on
				(("hierarchy"."child_team_id" = "child_team"."id"))
			left join "teams" as "team" on
				(("hierarchy"."parent_team_id" = "team"."id"))
			inner join "form_instance_teams" as "instance_team" on
				(("child_team"."id" = "instance_team"."team_id"
				and instance_team.source in ('Subject',
				'ParentTeam')
				and (instance_team.role in ('Member',
				'Manager')
				or instance_team.role is null)))
			inner join "form_instances" as "instance" on
				(("instance"."id" = "instance_team"."instance_id"))
			where
				not ("child_team"."type" = 'Role')
				and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
				and "instance"."tenant" = 'Marshall'
				and "instance"."module" = 'Coaching'
				and "instance"."created_at" >= '2019-06-01'
				and "instance"."created_at" <= '2020-05-14 23:59:59'
			group by
				"entityId") as "subQuery2" on
			(("subQuery2"."entityId" = "child_team"."id"))
		left join (
			select
				count(instance.id)::decimal as "cal-dp-val-5-1",
				"child_team"."id" as "entityId"
			from
				"teams" as "child_team"
			left join "team_hierarchy" as "hierarchy" on
				(("hierarchy"."child_team_id" = "child_team"."id"))
			left join "teams" as "team" on
				(("hierarchy"."parent_team_id" = "team"."id"))
			inner join "form_instance_teams" as "instance_team" on
				(("child_team"."id" = "instance_team"."team_id"
				and instance_team.source in ('Subject',
				'ParentTeam')
				and (instance_team.role in ('Member',
				'Manager')
				or instance_team.role is null)))
			inner join "form_instances" as "instance" on
				(("instance"."id" = "instance_team"."instance_id"))
			where
				not ("child_team"."type" = 'Role')
				and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
				and "instance"."tenant" = 'Marshall'
				and "instance"."module" = 'Coaching'
				and "instance"."created_at" >= '2019-06-01'
				and "instance"."created_at" <= '2020-05-14 23:59:59'
			group by
				"entityId") as "subQuery3" on
			(("subQuery3"."entityId" = "child_team"."id"))
		left join (
			select
				count(instance.id)::decimal as "cal-dp-val-5-2",
				"child_team"."id" as "entityId"
			from
				"teams" as "child_team"
			left join "team_hierarchy" as "hierarchy" on
				(("hierarchy"."child_team_id" = "child_team"."id"))
			left join "teams" as "team" on
				(("hierarchy"."parent_team_id" = "team"."id"))
			inner join "form_instance_teams" as "instance_team" on
				(("child_team"."id" = "instance_team"."team_id"
				and instance_team.source in ('Subject',
				'ParentTeam')
				and (instance_team.role in ('Member',
				'Manager')
				or instance_team.role is null)))
			inner join "form_instances" as "instance" on
				(("instance"."id" = "instance_team"."instance_id"))
			where
				not ("child_team"."type" = 'Role')
				and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46'
				and "instance"."tenant" = 'Marshall'
				and "instance"."module" = 'Coaching'
				and "instance"."created_at" >= '2019-06-01'
				and "instance"."created_at" <= '2020-05-14 23:59:59'
			group by
				"entityId") as "subQuery4" on
			(("subQuery4"."entityId" = "child_team"."id"))
		where
			not ("child_team"."type" = 'Role')
			and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46') as "subQuery5" on
		(("subQuery5"."entityId" = "child_team"."id"))
	where
		not ("child_team"."type" = 'Role')
		and "team"."id" = 'f8d483a3-63a7-4043-9926-d40125070b46') as "resultsQuery"