Not a Sunday

drop table if exists [#_tempInteractionsTable#]; drop table if exists [#_bobAccountsTable#]; drop table if exists [#_bobOpportunitiesTable#]; delete from tbl_EXPORT_Kellanova_SalesRepActivity where ownerId = and ownerType = and eai_batchId = declare @startDate date = ; declare @endDate date = ; select interactionId, partnerType + cast(partnerId as varchar) + cast(salesRepId as varchar) as partnerKey, interactionDetailId, detailDisposition, callsToClose, objectiveItem, salesRepId, salesRepFirstName + ' ' + salesRepLastName as salesRepFullName, case when salesRepPriorInteractionDate is null or datediff(day, salesRepPriorInteractionDate, interactionDate) > 365 then 'Y' else 'N' end as newAccount, cast(null as int) as itemCount, cast(null as int) as lineNbr into [#_tempInteractionsTable#] from tbl_DW_Interactions#_tableSuffix# with (nolock) where disposition != '?' and nullif(salesRepFirstName, '') is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; with x as ( select interactionId, interactionDetailId, count(*) over (partition by interactionId) as itemCount, row_number() over (partition by interactionId order by interactionDetailId) as lineNbr from [#_tempInteractionsTable#] ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [#_tempInteractionsTable#] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @oprClassifications TABLE (classificationId INT NOT NULL); insert into @oprClassifications (classificationId) select classificationId from tbl_OPR_Classifications with (nolock) where abbreviation = 'BOB' and ownerId = @ownerId and ownerType = @ownerType; declare @cdrClassifications TABLE (classificationId INT NOT NULL); insert into @cdrClassifications (classificationId) select classificationId from tbl_CDR_Classifications with (nolock) where abbreviation = 'BOB' and ownerId = @ownerId and ownerType = @ownerType; with bobAccounts as ( select salesRepId, salesRepFirstName + ' ' + salesRepLastName as salesRepFullName, 'OPR' as partnerType, operatorId as partnerId, 'OPR:' + cast(operatorId as varchar) as partnerKey from tbl_DW_CRMOperators#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and classificationId in (select classificationId from @oprClassifications) union all select cmUserId as salesRepId, cmFullName as salesRepFullName, 'CDR' as partnerType, cdr_recordId as partnerId, 'CDR:' + cast(cdr_recordId as varchar) as partnerKey from tbl_DW_CRMDistributors#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and classificationId in (select classificationId from @cdrClassifications) ) select salesRepId, salesRepFullName, partnerType, partnerId, partnerKey into [#_bobAccountsTable#] from bobAccounts; alter table [#_bobAccountsTable#] add constraint [pk_#_bobAccountsTable#] primary key (partnerType, partnerId); create index [idx_#_bobAccountsTable#_rep] on [#_bobAccountsTable#] (salesRepId); select partnerId, partnerType, opportunityId, case when statusCode = 'R' then 'NOSALE' when statusCode = 'C' then 'SALE' when stageId in () then 'ACTIVE_PLUS' when stageId in () then 'ACTIVE' else 'EARLY' end as bobStatus into [#_bobOpportunitiesTable#] from tbl_DW_Opportunities#_tableSuffix# with (nolock) where effectiveRepOwnerType = and coalesce(effectiveRepId, 0) != 5199829 and stageId in (); alter table [#_bobOpportunitiesTable#] add constraint [pk_#_bobOpportunitiesTable#] primary key (opportunityId); create index [idx_#_bobOpportunitiesTable#_part] on [#_bobOpportunitiesTable#] (partnerType, partnerId, bobStatus); declare @startDate date = ; declare @endDate date = ; declare @days int = datediff(day, @startDate, @endDate) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [#_tempInteractionsTable#] with (nolock) ) , interactionSummary as ( select salesRepId, salesRepFullName, count(distinct interactionId) as totalCalls, count(distinct partnerKey) as uniqueAccounts, count(distinct case when newAccount = 'Y' then partnerKey end) as newAccounts, count(distinct case when objectiveItem = 1 then interactionId end) as objectiveCalls, count(distinct interactionDetailId) as totalPresentations, count(case when detailDisposition in ('STD','LTO') then 1 end) as totalWins, count(case when detailDisposition = 'FU' then 1 end) as totalFollowUps, count(case when detailDisposition = 'NS' then 1 end) as totalLoses, coalesce(round(avg(cast(callsToClose as float)), 1), 0.0) as avgCallsToClose, coalesce(round(avg(case when lineNbr = 1 then cast(itemCount as float) end), 1), 0.0) as avgItemsPerCall from i group by salesRepId, salesRepFullName ) , interactionMetrics as ( select *, case when totalCalls > 0 then round(cast(totalCalls as float) / @weeks, 1) else 0.0 end as callsPerWeek, case when cast(totalWins + totalLoses as float) > 0 then round(cast(totalWins as float) / cast(totalWins + totalLoses as float), 3) else 0.0 end as closeRate, case when cast(totalWins + totalLoses + totalFollowUps as float) > 0 then round(cast(totalWins as float) / cast(totalWins + totalLoses + totalFollowUps as float), 3) else 0.0 end as efficiencyRate from interactionSummary ) , bobWithOpps as ( select bob.salesRepId, bob.partnerKey, case when opp.opportunityId is not null then bob.partnerKey end as opportunityAccount, case when opp.bobStatus in ('ACTIVE','ACTIVE_PLUS') then bob.partnerKey end as activeAccount, case when opp.bobStatus = 'ACTIVE_PLUS' then bob.partnerKey end as activePlusAccount, case when opp.bobStatus = 'SALE' then bob.partnerKey end as saleAccount, case when opp.bobStatus = 'NOSALE' then bob.partnerKey end as noSaleAccount from [#_bobAccountsTable#] bob with (nolock) left outer join [#_bobOpportunitiesTable#] opp with (nolock) on opp.partnerId = bob.partnerId and opp.partnerType = bob.partnerType ) , bobSummary as ( select salesRepId, count(distinct partnerKey) as totalAccountsBob, cast(count(distinct opportunityAccount) as float) as totalAccountsWithOppsBob, cast(count(distinct activeAccount) as float) as totalActiveOppsBob, cast(count(distinct activePlusAccount) as float) as totalActive20, cast(count(distinct saleAccount) as float) as totalSaleBob, cast(count(distinct noSaleAccount) as float) as totalNoSaleBob from bobWithOpps group by salesRepId ) , bobMetrics as ( select *, case when totalAccountsWithOppsBob > 0 then round(totalSaleBob / totalAccountsWithOppsBob, 3) else 0.0 end as closeRateBob, case when totalAccountsBob > 0 then round(totalAccountsWithOppsBob / cast(totalAccountsBob as float), 3) else 0.0 end as bobReach from bobSummary ) insert into tbl_EXPORT_Kellanova_SalesRepActivity ( ownerType, ownerId, eai_batchId, eai_lineNumber, sales_rep, calls, rep_calls_per_week, unique_accounts, new_accounts, objective_calls, items, close_rate, efficiency, calls_per_close, items_per_call, total_accounts_bob, total_accounts_with_opps_bob, total_active_opps_bob, total_active_20, total_sale_bob, total_no_sale_bob, close_rate_bob, bob_reach ) select as ownerType, as ownerId, as eai_batchId, row_number() over (order by im.salesRepFullName) as eai_lineNumber, im.salesRepFullName as sales_rep, im.totalCalls as calls, im.callsPerWeek as rep_calls_per_week, im.uniqueAccounts as unique_accounts, im.newAccounts as new_accounts, im.objectiveCalls as objective_calls, im.totalPresentations as items, im.closeRate as close_rate, im.efficiencyRate as efficiency, im.avgCallsToClose as calls_per_close, im.avgItemsPerCall as items_per_call, coalesce(bm.totalAccountsBob, 0) as total_accounts_bob, coalesce(bm.totalAccountsWithOppsBob, 0) as total_accounts_with_opps_bob, coalesce(bm.totalActiveOppsBob, 0) as total_active_opps_bob, coalesce(bm.totalActive20, 0) as total_active_20, coalesce(bm.totalSaleBob, 0) as total_sale_bob, coalesce(bm.totalNoSaleBob, 0) as total_no_sale_bob, coalesce(bm.closeRateBob, 0) as close_rate_bob, coalesce(bm.bobReach, 0) as bob_reach from interactionMetrics im left outer join bobMetrics bm on bm.salesRepId = im.salesRepId order by im.salesRepFullName; select * from tbl_EXPORT_Kellanova_SalesRepActivity with (nolock) where ownerId = and ownerType = and eai_batchId = order by eai_lineNumber update tbl_EAI_InboundBatches set loadedColumns = 'sales_rep,calls,rep_calls_per_week,unique_accounts,new_accounts,objective_calls,items,close_rate,efficiency,calls_per_close,items_per_call,total_accounts_bob,total_accounts_with_opps_bob,total_active_opps_bob,total_active_20,total_sale_bob,total_no_sale_bob,close_rate_bob,bob_reach' where ownerId = and ownerType = and batchId =

Exported #lsnumberformat(_exportCount)# sales rep activity rows