declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @now date = ; declare @atRiskMinDays int = ; with objectivesRaw as ( select o.ownerType, o.ownerId, o.objectiveId, o.objName, o.publishDate, o.unpublishDate, o.dueDate, datediff(day, @now, o.unpublishdate ) as daysLeft, datediff( day, o.publishDate, getDate() ) as daysElapsed, o.territoryId, o.quotaType, coalesce( o.totalQuota, 0 ) quota, coalesce( o.quotaAchieved, 0 ) quotaAchieved, case when m.fspro_userId is not null then coalesce( m.primaryTerritoryId, o.territoryId ) else 0 end as salesRepTerritoryId, m.fspro_userId as salesRepId, m.fsl_tablecode as saleRepOwnerType, osl.quota as salesRepQuota, osl.quotaAchieved as salesRepQuotaAchieved, m.firstName + ' ' + m.lastName as salesRepFullName, qt.description as quotaTypeDescription from tbl_BCRM_Objectives o with (nolock) inner join tbl_bcrm_quotatypes qt with (nolock) on qt.quotatype = o.quotatype and qt.objtype = o.objtype left outer join tbl_BCRM_ObjectiveStfLink osl with (nolock) on osl.ownerId = o.ownerId and osl.ownerType = o.ownerType and osl.objectiveId = o.objectiveId left outer join tbl_Fspro_Members m with (nolock) on m.fspro_userId = osl.fspro_userId left outer join tbl_BCRM_ObjectiveBundles bun with (nolock) on bun.bundleid = o.bundleid where o.ownerId = @ownerId and o.ownerType = @ownerType and ( ( o.publishDate <= @now and dateadd( day, #_expiredViewExtension#, o.unpublishDate ) >= @now ) ) and o.ready = 'Y' and coalesce( m.primaryTerritoryId, o.territoryId ) in ( ) and m.fsl_tablecode = ), objectives as ( select o.*, ot.territoryPath, ot.name as territoryName, rt.territoryPath as salesRepTerritoryPath, rt.name as salesRepTerritoryName, sum( salesRepQuota ) over ( partition by o.ownerType, o.ownerId, o.objectiveId ) as staffQuota, sum( salesRepQuotaAchieved ) over ( partition by o.ownerType, o.ownerId, o.objectiveId ) as staffQuotaAchieved from objectivesRaw o with (nolock) left outer join tbl_TER_TErritories ot with (nolock) on ot.territoryId = o.territoryId and ot.ownerId = o.ownerId and ot.fsl_tablecode = o.ownerType left outer join tbl_TER_TErritories rt with (nolock) on rt.territoryId = o.salesRepTerritoryId and rt.ownerId = o.ownerId and rt.fsl_tablecode = o.ownerType where 1 = 1 and rt.territoryPath like and o.salesRepId in ( ) and o.salesRepId = ), objectivesWithStatus as ( -- using #_level# quota and quotaAchieved select o.*, case when o.quota = 0 or o.quotaAchieved >= o.quota then 'CP' when o.salesRepQuota = 0 or o.salesRepQuotaAchieved >= o.salesRepQuota then 'CP' when o.staffQuota = 0 or o.staffQuotaAchieved >= o.staffQuota then 'CP' when o.daysLeft <= @atRiskMinDays then 'AR' else 'IP' end status from objectives o with (nolock) ), objectivesByStatus as ( select distinct objectiveId, status from objectivesWithStatus with ( nolock ) ), resultRaw as ( select status, count( * ) as objectiveCount from objectivesByStatus with ( nolock ) group by status ), result as ( select sum( case when status = 'AR' then objectiveCount else 0 end ) as AR, sum( case when status = 'IP' then objectiveCount else 0 end ) as IP, sum( case when status = 'CP' then objectiveCount else 0 end ) as CP from resultRaw with (nolock) ), data as ( select status, objectiveCount from ( select CP, IP, AR from result ) s unpivot ( objectiveCount for status in ( CP, IP, AR )) as unpvt ) select d.status, coalesce( d.objectiveCount, 0 ) objectiveCount, '#_level#' source, case when d.status = 'IP' then 1 when d.status = 'CP' then 2 else 3 end as displayOrder , ( select distinct ows.objectiveId, ows.objName, ows.territoryName, ows.publishDate, ows.unpublishDate, ows.dueDate, ows.quotaType, ows.quotaTypeDescription, ows.daysLeft, ows.daysElapsed, ows.quota quota, ows.quotaAchieved achieved, case when ows.status = 'CP' then 1.0 else case when ows.quota != 0 then ows.quotaAchieved / cast( ows.quota as float ) else null end end as achievement ows.staffQuota quota, ows.staffQuotaAchieved achieved, case when ows.status = 'CP' then 1.0 else case when ows.staffQuota != 0 then ows.staffQuotaAchieved / cast( ows.staffQuota as float ) else null end end as achievement ows.salesRepQuota quota, ows.salesRepQuotaAchieved achieved, case when ows.status = 'CP' then 1.0 else case when ows.salesRepQuota != 0 then ows.salesRepQuotaAchieved / cast( ows.salesRepQuota as float ) else null end end as achievement from objectivesWithStatus ows where ows.status = d.status order by dueDate, achievement, objName for json path ) as objectives from data d where d.status = order by displayOrder for json auto, include_null_values