declare @ownerId int = ; declare @ownerType varchar(3) = ; with salesRepVisibility as ( select distinct s.ownerId, s.ownerType, s.objectiveId from tbl_BCRM_ObjectiveStfLink s with (nolock) where s.ownerId = @ownerId and s.ownerType = @ownerType and s.fspro_userId in ( ) ), staffMemberObjectives as ( select s.objectiveId from tbl_BCRM_ObjectiveStfLink s with (nolock) where s.ownerId = @ownerId and s.ownerType = @ownerType and s.fspro_userId in ( ) ), delegatedQuotas as ( select ownerId, ownerType, objectiveId, sum(quota) as delegatedQuota from tbl_bcrm_objectivestflink osl with (nolock) where osl.ownerid = @ownerId and osl.ownertype = @ownerType group by ownerId, ownerType, objectiveId ), parentDelegatedQuotas as ( select osl.ownerId, osl.ownerType, o.parentObjectiveId, sum( osl.quota) as delegatedQuota from tbl_bcrm_objectivestflink osl with (nolock) inner join tbl_BCRM_Objectives o with (nolock) on o.ownerId = osl.ownerId and o.ownerType = osl.ownerType and o.objectiveId = osl.objectiveID where osl.ownerid = @ownerId and osl.ownertype = @ownerType and o.parentObjectiveId > 0 group by osl.ownerId, osl.ownerType, o.parentObjectiveId ), quotaTypes as ( select objType, quotaType, description, volumeRelated from tbl_bcrm_quotatypes with (nolock) ), targetStatsBase as ( select o.ownerId, o.ownerType, o.parentObjectiveId, o.objectiveId, -- otl.linkId targetLinkId, otl.targetId, otl.targetType, osl.linkId staffLinkId, osl.fspro_userId, count( distinct otl.linkId) totalTargets, count( distinct otl.linkId ) - count( distinct orta.achievementId ) remainingTargets, count( distinct i.interactionId ) as callCount, sum( case when i.disposition != '?' then 1 else 0 end ) as completedCalls, sum( case when i.disposition = '?' then 1 else 0 end ) as plannedCalls, sum( case when nullif( orta.achievement, 0 ) is null and i.disposition = '?' and i.interactionDate is not null then 1 else 0 end ) as noAchievementPlannedCalls, sum( case when nullif( orta.achievement, 0 ) is null and i.disposition = '?' and i.interactionDate is null then 1 else 0 end ) as noAchievementUnplannedCalls, ( select sum( otlx.callsToAchievement ) from tbl_BCRM_ObjectiveTrgLink otlx with (nolock) where otlx.ownerId = o.ownerId and otlx.ownerType = o.ownerType and otlx.objectiveId = o.objectiveId ) as callsToAchievement -- , min( case when i.disposition = '?' then i.interactionDate else null end ) as nextCall from tbl_BCRM_ObjectiveTrgLink otl with (nolock) inner join tbl_BCRM_Objectives o with (nolock) on o.objectiveId = otl.objectiveId and o.ownerId = otl.ownerId and o.ownerType = otl.ownerType inner join tbl_BCRM_ObjectiveStfLink osl with (nolock) on osl.ownerId = osl.ownerId and osl.ownerType = otl.ownerType and osl.objectiveId = otl.objectiveId inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.ownerid = otl.ownerId and ip.ownertype = otl.ownerType and ip.participantId = otl.linkId inner join tbl_CRM_interactions i with (nolock) on i.ownerId = ip.ownerId and i.ownerType = ip.ownerType and i.interactionId = ip.interactionId and i.dispositionUserId = osl.fspro_userId left outer join tbl_BCRM_ObjectiveRepTrgAchievement orta (nolock) on orta.objectiveId = otl.objectiveId and orta.targetId = otl.targetId and orta.targetType = otl.targetType and orta.fspro_userId = osl.fspro_userId and orta.ownerId = otl.ownerId and orta.ownerType = otl.ownerType and coalesce( orta.achievement, 0 ) > 0 where otl.ownerId = @ownerId and otl.ownerType = @ownerType and 1 = 1 group by o.ownerId, o.ownerType, o.parentObjectiveId, o.objectiveId ), targetStats as ( select ownerId, ownerType, objectiveId, totalTargets, remainingTargets, callCount, completedCalls, plannedCalls, noAchievementPlannedCalls, noAchievementUnplannedCalls, callsToAchievement from targetStatsBase with (nolock) union all select ownerId, ownerType, parentObjectiveId as objectiveId, sum( totalTargets) totalTargets, sum( remainingTargets ) remainingTargets, sum( callCount ) callCount, sum( completedCalls ) completedCalls, sum( plannedCalls ) plannedCalls, sum( noAchievementPlannedCalls ) noAchievementPlannedCalls, sum( noAchievementUnplannedCalls) noAchievementUnplannedCalls, sum( callsToAchievement ) callsToAchievement from targetStatsBase with (nolock) where parentObjectiveId > 0 group by ownerId, ownerType, parentObjectiveId ), data as ( select o.objectiveId,o.objName,o.objDescription,o.objType,o.targetType,o.dueDate,o.publishDate,o.unpublishDate,o.ready,o.objLevel, o.objPath, t.territoryId, t.name as territoryName, t.territoryPath, cast( o.objOwnerType as varchar ) objOwnerType, o.objOwnerId, case when datediff( day, getDate(), o.publishDate ) > 0 then 'Future' when datediff( day, getDate(), o.unpublishDate ) < 0 then 'Past' else 'Active' end as status, case when o.objOwnerType = 'MFR' then mo.mfr_name else 'Company Internal' end as objOwnerName, ob.bundleId, ob.bundleName, po.objectiveId as parentObjectiveId, case when po.objName is not null then po.objName when o.objLevel = 'P' then o.objName else null end as parentObjName, omgr.fspro_userId as objManagerId, omgr.firstName + ' ' + omgr.lastName objManagerFullName, omgr.email as objManagerEmail, omgr2.fspro_userId as objManager2Id, omgr2.firstName + ' ' + omgr2.lastName objManager2FullName, omgr2.email as objManager2Email, omgr3.fspro_userId as objManager3Id, omgr3.firstName + ' ' + omgr3.lastName objManager3FullName, omgr3.email as objManager3Email, o.quotaType, o.totalQuota, case when o.objLevel = 'P' then pdq.delegatedQuota else dq.delegatedQuota end as delegatedQuota, o.quotaAchieved, nullif( o.ownerRefNum, '' ) ownerRefNum, o.limitParticipation, case when o.limitParticipation = 0 then 1 else 0 end fullParticipation, o.allowAutoTargetLinks, o.allowRepTargeting, coalesce( o.salesOpsTargeting, 0 ) as salesOpsTargeting, o.allowRepUnenrollment, o.allowProtectBaseStatus, o.allowFollowUpStatus, o.batchQuotaSetTargetCount, o.quotaDurationUnit, qt.description as quotaTypeName, case when qt.volumeRelated = 1 then cast( 1 as bit ) else cast( 0 as bit ) end as isVolumeRelated, -- oprSegmentRestrict, ( select json_query( '[' + string_agg( restrictId,',' ) + ']' ) from tbl_BCRM_ObjRestrictions orx where orx.ownerId = o.ownerId and orx.ownerType = o.ownerType and orx.objectiveId = o.objectiveId and orx.restrictType = 'SEG' ) as oprSegmentRestrict, -- oprParentDistributorRestrict, ( select json_query( '[' + string_agg( restrictId,',' ) + ']' ) from tbl_BCRM_ObjRestrictions orx where orx.ownerId = o.ownerId and orx.ownerType = o.ownerType and orx.objectiveId = o.objectiveId and orx.restrictType = 'PDST' ) as oprParentDistributorRestrict, -- oprPriorityRestrict, ( select json_query( '[' + string_agg( restrictId,',' ) + ']' ) from tbl_BCRM_ObjRestrictions orx where orx.ownerId = o.ownerId and orx.ownerType = o.ownerType and orx.objectiveId = o.objectiveId and orx.restrictType = 'CLAS' ) as oprPriorityRestrict, nullif( o.oprDistributorRestrict, 0 ) oprDistributorRestrict, o.oprTypeRestrict, o.mfrOprTypeRestrict, nullif( o.oprAffiliationRestrict, 0 ) oprAffiliationRestrict, o.oprAssignedOnlyRestrict, o.unenrollmentNotifyOwner, o.limitAutoLinkingToTerritory, ts.completedCalls, ts.remainingTargets, ts.noAchievementPlannedCalls, ts.noAchievementUnplannedCalls, ts.callsToAchievement, case when ts.remainingTargets > 0 then ts.noAchievementPlannedCalls / cast( ts.remainingTargets as float ) else null end as remainingTargetsPlannedPct, case when ts.remainingTargets > 0 then 1.0 - ( ts.noAchievementPlannedCalls / cast( ts.remainingTargets as float ) ) else null end as remainingTargetsUnplannedPct, case when not( o.ready = 'Y' and getDate() between o.publishDate and o.unpublishDate ) then 0 else coalesce( o.faultCount, 0 ) end as faultCount from tbl_BCRM_Objectives o with (nolock) left outer join tbl_BCRM_Objectives po with (nolock) on po.ownerId = o.ownerId and po.ownerType = o.ownerType and po.objectiveId = o.parentObjectiveId left outer join tbl_BCRM_ObjectiveBundles ob with (nolock) on ob.ownerId = o.ownerId and ob.ownerType = o.ownerType and ob.bundleId = o.bundleId left outer join tbl_FSPro_Members omgr with (nolock) on omgr.fspro_userId = o.objManagerId left outer join tbl_FSPro_Members omgr2 with (nolock) on omgr2.fspro_userId = o.objManager2Id left outer join tbl_FSPro_Members omgr3 with (nolock) on omgr3.fspro_userId = o.objManager3Id left outer join tblManufacturers mo with (nolock) on mo.mfr_id = o.objOwnerId and 'MFR' = o.objOwnerType left outer join tbl_TER_TErritories t with (nolock) on t.ownerId = o.ownerId and t.fsl_tablecode = o.ownerType and t.territoryId = o.territoryId left outer join delegatedQuotas dq with (nolock) on dq.ownerId = o.ownerId and dq.ownerType = o.ownerType and dq.objectiveId = o.objectiveId left outer join parentDelegatedQuotas pdq with (nolock) on pdq.ownerId = o.ownerId and pdq.ownerType = o.ownerType and pdq.parentObjectiveId = o.objectiveId left outer join quotaTypes qt with (nolock) on qt.objType = o.objType and qt.quotaType = o.quotaType left outer join targetStats ts with (nolock) on ts.ownerId = o.ownerId and ts.ownerType = o.ownerType and ts.objectiveId = o.objectiveId where o.ownerId = @ownerId and o.ownerType = @ownerType and ( nullif( o.territoryId, 0 ) is null or o.territoryId in ( )) and o.objectiveId in ( select objectiveId from salesRepVisibility ) and ( exists ( select 1 from tbl_BCRM_ObjectiveStfLink s with (nolock) where s.ownerId = @ownerId and s.ownerType = @ownerType and s.objectiveId = o.objectiveId and s.fspro_userId in ( ) ) or ( o.objManagerId in ( )) ) ), dataPlus as ( select *, case when totalQuota > 0 then cast( coalesce( quotaAchieved, 0 ) as float ) / cast( totalQuota as float ) case when cast( coalesce( quotaAchieved, 0 ) as float ) / cast( totalQuota as float ) > 1.0 then 1.0 else cast( coalesce( quotaAchieved, 0 ) as float ) / cast( totalQuota as float ) end else cast( null as float ) end as quotaAchievementPct, totalQuota - delegatedQuota as remainingQuota, case when objLevel = 'P' then 'ENT' -- 'Enterprise' when objLevel = 'C' then 'SUB' -- 'Sub-Objective' when objLevel = 'S' then 'SA' -- 'Stand-Alone' else objLevel end as objLevelName, case when cast( getDate() as date ) between publishDate and unpublishDate then datediff( day, getDate(), unpublishDate ) else cast( null as int ) end as daysRemaining from data ) #forJSON( 'dataPlus', attributes.datastore, _defaultSortOrder )#