select unpublishDate, publishDate, ready, case when ready = 'Y' and getDate() between publishDate and unpublishDate then 'Y' else 'N' end as includeWarnings from tbl_BCRM_Objectives where ownerId = and ownerType = and objectiveId = and objLevel in ( 'C', 'S' ) declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @objectiveId int = ; declare @objectiveEndDate datetime = ; with objTargetsBase as ( -- targetCounts select otl.ownerId, otl.ownerType, otl.objectiveId, otl.linkId targetLinkId, otl.targetComment, otl.targetId, otl.targetType, osl.linkId staffLinkId, osl.fspro_userId, coalesce( otl.callsToAchievement, 0 ) callsToAchievement, 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, min( case when i.disposition = '?' then i.interactionDate else null end ) as nextCall from tbl_BCRM_ObjectiveTrgLink otl with (nolock) 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 otl.objectiveId = @objectiveId group by otl.ownerId, otl.ownerType, otl.objectiveId, otl.linkId, otl.targetId, otl.targetType, otl.targetComment, osl.fspro_userId, osl.linkId, otl.callsToAchievement --order by otl.objectiveId ) , objTargetAchievement as ( select otl.ownerId, otl.ownerType, otl.objectiveId, otl.targetId, otl.targetType, osl.fspro_userId, sum( orta.achievement ) targetAchievement from tbl_BCRM_ObjectiveTrgLink otl with (nolock) 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_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 orta.achievement > 0 where otl.ownerId = @ownerId and otl.ownerType = @ownerType and otl.objectiveId = @objectiveId group by otl.ownerId, otl.ownerType, otl.objectiveId, otl.targetId, otl.targetType, osl.fspro_userId ) , objTargetNoAchievement as ( -- no achievement breakdown select otl.ownerId, otl.ownerType, otl.objectiveId, otl.targetId, otl.targetType, osl.fspro_userId, sum( case when i.interactionDate is null then 1 else 0 end ) as noAchievementUnplannedCalls, sum( case when i.interactionDate is not null then 1 else 0 end ) as noAchievementPlannedCalls from tbl_BCRM_ObjectiveTrgLink otl with (nolock) 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 orta.achievement > 0 where otl.ownerId = @ownerId and otl.ownerType = @ownerType and otl.objectiveId = @objectiveId and orta.achievement is null group by otl.ownerId, otl.ownerType, otl.objectiveId, otl.targetId, otl.targetType, osl.fspro_userId --order by otl.objectiveId ) , objTargetWarnings as ( select *, concat_ws( ',', case when dateFault = 1 then 'Outside Objective Dates' else null end, case when productFault = 1 then 'No Matching Products' else null end, case when typeCodeFault = 1 then 'Existing Business not allowed' else null end, case when dispositionFault = 1 then 'Follow Up not allowed' else null end ) as warningCodes from tbl_bcrm_objectivereptrgnonachievement with (nolock) where objectiveId = @objectiveId and ownerId = @ownerId and ownerType = @ownerType and 1 = 2 ) , objTargetVolume as ( select partnerId, partnerType, dispositionUserId, sum( cases ) cases, sum( lbs) lbs, sum( dollars ) dollars, sum( impactCases ) impactCases, sum( impactLbs) impactLbs, sum( impactDollars ) impactDollars from [tbl_DW_Interactions#_tableSuffix#] with (nolock) where @objectiveId in ( objectiveId1, objectiveId2, objectiveId3, objectiveId4, objectiveId5 ) and disposition != '?' and detailDisposition = 'STD' group by partnerId, partnerType, dispositionUserId ) , objTargetActivityRaw as ( select t.ownerId, t.ownerType, t.objectiveId, t.targetId, t.targetType, isd.dispositionUserId as fspro_userId, case when isd.detailDisposition = 'STD' then 1 else 0 end as sale, case when isd.detailDisposition = 'STD' then isd.interactionDate else null end as saleDate, case when isd.detailDisposition = 'NS' then 1 else 0 end as nosale, case when isd.detailDisposition = 'NS' then isd.interactionDate else null end as noSaleDate, case when isd.detailDisposition not in ( 'STD', 'NS' ) then 1 else 0 end as other, case when isd.detailDisposition not in ( 'STD', 'NS' ) then isd.interactionDate else null end as otherDate, case when isd.detailDisposition = 'STD' then case when coalesce( o.allowProtectBaseStatus, 0 ) = 0 then case when isd.typeCode = 'PRO' then 'FAILURE' else 'SUCCESS' end else 'SUCCESS' end when isd.detailDisposition = 'NS' then 'FAILURE' else 'CONTINUE' end as targetStatus from tbl_BCRM_ObjectiveTrgLink t with (nolock) inner join tbl_BCRM_Objectives o with (nolock) on o.ownerId = t.ownerId and o.ownerType = t.ownerType and o.objectiveId = t.objectiveId inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.ownerId = t.ownerId and ip.ownertype = t.ownerType and ip.participantId = t.linkId inner join tbl_BCRM_ObjectivePrdLink p with (nolock) on p.ownerId = t.ownerId and p.ownerType = t.ownerType and p.objectiveId = t.objectiveId inner join [tbl_DW_Interactions#_tableSuffix#] isd with (nolock) on isd.ownerId = ip.ownerId and isd.ownerType = ip.ownerType and isd.interactionId = ip.interactionId and case when isd.skuId > 0 then 'SKU' when isd.prodId > 0 then 'PRD' else null end = case when p.skuId > 0 then 'SKU' when p.categoryId > 0 then 'PRD' else null end and case when isd.skuId > 0 then isd.skuId when isd.prodId > 0 then isd.prodId else null end = case when p.skuId > 0 then p.skuId when p.categoryId > 0 then p.categoryId else null end and isd.disposition != '?' where t.ownerId = @ownerId and t.ownertype = @ownerType and t.objectiveId = @objectiveId ) , objTargetActivity as ( select x.ownerId, x.ownerType, x.objectiveId, x.targetId, x.targetType, x.fspro_userId, sum( sale ) completedSaleCount, max( saleDate ) as completedSaleDate, sum( noSale ) completedNoSaleCount, max( noSaleDate ) completedNoSaleDate, sum( other ) completedOtherCount, max( otherDate ) completedOtherDate, sum( case when targetStatus = 'SUCCESS' then 1 else 0 end ) as successCount, sum( case when targetStatus = 'FAILURE' then 1 else 0 end ) as failureCount, sum( case when targetStatus = 'CONTINUE' then 1 else 0 end ) as continueCount from objTargetActivityRaw x group by x.ownerType, x.ownerId, x.objectiveId, x.targetId, x.targetType, x.fspro_userId ) , objTargetsRaw as ( select t.*, a.targetAchievement, na.noAchievementPlannedCalls, na.noAchievementUnplannedCalls, v.cases, v.lbs, v.dollars, v.impactCases, v.impactLbs, v.impactDollars , ta.completedSaleCount, ta.completedSaleDate, ta.completedNoSaleCount, ta.completedNoSaleDate, ta.completedOtherCount, ta.completedOtherDate , ta.successCount, ta.failureCount, ta.continueCount , tw.warningCodes, tw.faultCount, tw.faultCodes from objTargetsBase t left outer join objTargetAchievement a on a.ownerId = t.ownerId and a.ownertype = t.ownerType and a.objectiveId = t.objectiveId and a.targetId = t.targetId and a.targetType = t.targetType and a.fspro_userId = t.fspro_userId left outer join objTargetNoAchievement na on na.ownerId = t.ownerId and na.ownertype = t.ownerType and na.objectiveId = t.objectiveId and na.targetId = t.targetId and na.targetType = t.targetType and na.fspro_userId = t.fspro_userId left outer join objTargetActivity ta on ta.ownerId = t.ownerId and ta.ownertype = t.ownerType and ta.objectiveId = t.objectiveId and ta.targetId = t.targetId and ta.targetType = t.targetType and ta.fspro_userId = t.fspro_userId left outer join objTargetWarnings tw on tw.ownerId = t.ownerId and tw.ownerType = t.ownerType and tw.objectiveId = t.objectiveId and tw.targetId = t.targetId and tw.targetType = t.targetType and tw.fspro_userId = t.fspro_userId left outer join objTargetVolume v on v.partnerId = t.targetId and v.partnerType = t.targetType and v.dispositionUserId = t.fspro_userId ) , objStaff1 as ( select o.ownerId, o.ownerType, o.objectiveId, o.objName, s.linkId as staffLinkId, s.fspro_userId, s.quota, s.quotaAchieved, m.firstName, m.lastName, m.email, sum( t.totalTargets ) as totalTargets, sum( t.remainingTargets ) as remainingTargets, sum( t.noAchievementPlannedCalls ) noAchievementPlannedCalls, sum( t.noAchievementUnplannedCalls ) noAchievementUnplannedCalls, sum( t.completedCalls ) as completedCalls, sum( t.callsToAchievement ) as callsToAchievement, sum( t.cases ) as cases, sum( t.lbs ) as lbs, sum( t.dollars ) as dollars, sum( t.impactcases ) as impactCases, sum( t.impactlbs ) as impactLbs, sum( t.impactdollars ) as impactDollars from tbl_BCRM_Objectives o with (nolock) inner join tbl_BCRM_ObjectiveStfLink s with (nolock) on s.objectiveId = o.objectiveId and s.ownerType = o.ownerType and s.ownerId = o.ownerId inner join tbl_FSPRO_Members m with (nolock) on m.ownerId = s.ownerId and m.fsl_tablecode = s.ownerType and m.fspro_userId = s.fspro_userId left outer join objTargetsRaw t on t.ownerId = s.ownerId and t.ownertype = s.ownerType and t.staffLinkId = s.linkId where o.ownerId = @ownerId and o.ownertype = @ownerType and o.objectiveId = @objectiveId group by o.ownerId, o.ownerType, o.objectiveId, o.objName, s.linkId, s.fspro_userId, s.quota, s.quotaAchieved, m.firstName, m.lastName, m.email ) , objStaff as ( select *, case when quota > 0 then quotaAchieved / cast( quota as float ) else null end as quotaAchievement , case when remainingTargets > 0 then noAchievementPlannedCalls / cast( remainingTargets as float ) else null end as remainingTargetsPlannedPct , case when remainingTargets > 0 then 1.0 - ( noAchievementPlannedCalls / cast( remainingTargets as float ) ) else null end as remainingTargetsUnplannedPct from objStaff1 ) -- , objTargetsRaw as ( -- select distinct o.ownerId, o.ownerType, o.objectiveId, o.objName, t.linkId as targetLinkId, t.targetType, t.targetId, s.fspro_userId, s.linkId as staffLinkId, -- sum( case when i.disposition = '?' then 0 else 1 end ) over ( partition by t.linkId, s.linkId ) as completedCalls, -- min( case when i.disposition = '?' then i.interactionDate else null end ) over ( partition by t.linkId, s.linkId ) as nextCall, -- count( * ) over ( partition by t.linkId, s.linkId ) as callCount, first_value( i.disposition ) over ( partition by t.linkId, s.linkId order by i.interactionDate ) firstDisposition, first_value( i.disposition ) over ( partition by t.linkId, s.linkId order by i.interactionDate desc ) lastDisposition -- from tbl_BCRM_Objectives o with (nolock) -- inner join tbl_BCRM_ObjectiveStfLink s with (nolock) -- on s.objectiveId = o.objectiveId -- and s.ownerType = o.ownerType -- and s.ownerId = o.ownerId -- inner join tbl_BCRM_ObjectiveTrgLink t with (nolock) -- on t.objectiveId = o.objectiveId -- and t.ownerType = o.ownerType -- and t.ownerId = o.ownerId -- inner join tbl_CRM_InteractionParticipants ip with (nolock) -- on ip.ownerType = o.ownerType -- and ip.ownerId = o.ownerId -- and ip.participantId = t.linkId -- inner join tbl_CRM_Interactions i with (nolock) -- on i.ownerType = ip.ownerType -- and i.ownerId = ip.ownerId -- and i.interactionId = ip.interactionId -- and i.dispositionUserId = s.fspro_userId -- and i.partnerId = t.targetId -- and i.partnerType = t.targetType -- where o.ownerId = @ownerId -- and o.ownertype = @ownerType -- and o.objectiveId = @objectiveId -- ) , objTargetsAndPartners as ( select t.*, o.operatorId partnerId, 'OPR' partnerType, o.companyName as partnerName from objTargetsRaw t with (nolock) inner join [tbl_DW_CRMOperators#_tableSuffix#] o with (nolock) on o.ownerId = t.ownerId and o.ownerType = t.ownerType and o.operatorId = t.targetId where t.targetType = 'OPR' union all select t.*, d.cdr_recordId partnerId, 'CDR' partnerType, d.cdr_dstName as partnerName from objTargetsRaw t with (nolock) inner join [tbl_DW_CRMDistributors#_tableSuffix#] d with (nolock) on d.ownerId = t.ownerId and d.ownerType = t.ownerType and d.cdr_recordId = t.targetId where t.targetType in ( 'CDR', 'DST' ) ), -- objTargetsAndAchievment as ( -- select t.ownerId, t.ownerType, t.objectiveId, t.targetLinkId, t.targetType, t.targetId, t.staffLinkId, -- t.completedCalls, t.callCount, -- -- t.firstDisposition, t.lastDisposition, -- t.partnerId, t.partnerType, t.partnerName,t.nextCall, -- count(AchievementID) targetAchievementCount, sum( ta.achievement ) targetAchievement -- from objTargetsAndPartners t with (nolock) -- left outer join tbl_bcrm_objectivereptrgachievement ta with (nolock) -- on ta.ownerId = t.ownerId -- and ta.ownertype = t.ownerType -- and ta.objectiveId = t.objectiveId -- and ta.fspro_userId = t.fspro_userId -- and ta.targetType = t.targetType -- and ta.targetId = t.targetId -- group by t.ownerId, t.ownerType, t.objectiveId, t.targetLinkId, t.targetType, t.targetId, t.staffLinkId, -- t.completedCalls, -- t.callCount, -- -- t.firstDisposition, t.lastDisposition, -- t.partnerId, t.partnerType, t.partnerName, t.nextCall -- ), objTargets as ( select ta.*, ( select i.interactionId, i.disposition, i.interactionDate, i.dispositionUserId from tbl_BCRM_ObjectiveTrgLink t with (nolock) inner join tbl_BCRM_ObjectiveStfLink s with (nolock) on s.objectiveId = t.objectiveId and s.ownerType = t.ownerType and s.ownerId = t.ownerId inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.ownerType = t.ownerType and ip.ownerId = t.ownerId and ip.participantId = t.linkId inner join tbl_CRM_Interactions i with (nolock) on i.ownerType = ip.ownerType and i.ownerId = ip.ownerId and i.interactionId = ip.interactionId and i.dispositionUserId = s.fspro_userId and i.partnerId = t.targetId and i.partnerType = t.targetType where t.objectiveId = ta.objectiveId and t.ownerType = ta.ownerType and t.ownerId = ta.ownerId and t.linkId = ta.targetLinkId and s.linkId = ta.staffLinkId order by i.interactionDate for json path, include_null_values ) as interactions from objTargetsAndPartners ta with (nolock) ) , objStaffWithTargets as ( select s.*, s.firstName + ' ' + s.lastName as fullName, ( select t.targetLinkId, t.targetComment, t.partnerId, t.partnerType, t.partnerName, t.completedCalls, t.callCount, t.nextCall, t.completedSaleCount, t.completedSaleDate, t.completedNoSaleCount, t.completedNoSaleDate, t.completedOtherCount, t.completedOtherDate, t.failureCount, t.continueCount, t.successCount, t.callsToAchievement, case when nullif( t.targetAchievement, 0 ) is null then case when datediff( day, getDate(), @objectiveEndDate ) < 0 then 'Not Completed' when t.failureCount > 0 and t.continueCount = 0 then 'Completed' when t.callCount > 0 and t.nextCall is not null then 'In Progress' else 'Not Planned' end else 'Completed' end as targetStatus, -- t.firstDisposition, t.lastDisposition, t.targetAchievementCount, t.targetAchievement, t.interactions, t.warningCodes, t.faultCount, t.faultCodes from objTargets t with (nolock) where t.ownerId = s.ownerId and t.ownerType = s.ownerType and t.objectiveId = s.objectiveId and t.staffLinkId = s.staffLinkId order by partnerName for json path, include_null_values ) as targets, coalesce( ( select sum( t.faultCount ) from objTargets t with (nolock) where t.ownerId = s.ownerId and t.ownerType = s.ownerType and t.objectiveId = s.objectiveId and t.staffLinkId = s.staffLinkId ), 0 ) as faultCount from objStaff s with (nolock) ) #forJSON( 'objStaffWithTargets', attributes.datastore, 'lastName,firstName' )#