declare @startDate date = ; -- #lsDateFormat( _objectParams.interactionDateStart, 'MM/dd/yyyy' )# declare @endDate date = ; -- #lsDateFormat( _interactionDateEnd, 'MM/dd/yyyy' )# -- drop table if exists [#_tempInteractionsTable#]; select interactionId, partnerType + cast( partnerId as varchar ) + cast( salesRepId as varchar ) as partnerKey, interactionDetailId, prodId, detailDisposition, callsToClose, objectiveItem, salesRepId, segmentPath as clientSegPath , case when salesRepPriorInteractionDate is null or datediff( day, salesRepPriorInteractionDate, interactionDate ) > 365 then 'Y' else 'N' end as newAccount , cast( null as int ) itemCount --count(*) over ( partition by interactionId ) itemCount , cast( null as int ) lineNbr --row_number() over ( partition by interactionId order by interactionDetailId ) lineNbr , '#_groupType#' as groupType , #_correlationColumn# as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , #_groupColumn# as groupDisplay , '#_groupColumn#' as groupColumn , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [#_tempInteractionsTable#] from tbl_DW_Interactions#_tableSuffix# with (nolock) where disposition != '?' and territoryId in ( ) and salesRepId in ( ) and mfr_id in ( ) and salesRepId in ( ) and salesRepOwnerType = and salesRepActive = 'Y' and ( 1 = 2 or territoryPath like ) and interactionMethod in ( ) and (1=2 or segmentPath like ) segmentPath like and partnerType = and nullif( salesRepFirstName, '' ) is not null and nullif( regionName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [#_tempInteractionsTable#] add constraint [PK#_tempInteractionsTable#] primary key ( interactionId, interactionDetailId ); with x as ( select i.interactionId, i.interactionDetailId, count(*) over ( partition by interactionId ) as itemCount, row_number() over ( partition by interactionId order by interactionDetailId ) lineNbr from [#_tempInteractionsTable#] i ) 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; select distinct groupType, groupId, groupDisplay, groupColumn into [#_tempGroupsTable#] from [#_tempInteractionsTable#] with (nolock); declare @startDate date = ; -- #lsDateFormat( _objectParams.interactionDateStart, 'MM/dd/yyyy' )# declare @endDate date = ; -- #lsDateFormat( _interactionDateEnd, 'MM/dd/yyyy' )# declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; select interactionId, partnerType + cast( partnerId as varchar ) + cast( salesRepId as varchar ) as partnerKey, interactionDetailId, prodId, detailDisposition, callsToClose, objectiveItem, salesRepId, segmentPath as clientSegPath , case when salesRepPriorInteractionDate is null or datediff( day, salesRepPriorInteractionDate, interactionDate ) > 365 then 'Y' else 'N' end as newAccount , count(*) over ( partition by interactionId ) itemCount , row_number() over ( partition by interactionId order by interactionDetailId ) lineNbr , '#_groupType#' as groupType , #_correlationColumn# as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , #_groupColumn# as groupDisplay , '#_groupColumn#' as groupColumn , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [#_tempInteractionsTable#] from tbl_DW_Interactions#_tableSuffix# with (nolock) where disposition != '?' and territoryId in ( ) and salesRepId in ( ) and mfr_id in ( ) and salesRepId in ( ) and salesRepOwnerType = and salesRepActive = 'Y' and ( 1 = 2 or territoryPath like ) and interactionMethod in ( ) and (1=2 or segmentPath like ) segmentPath like and partnerType = and nullif( salesRepFirstName, '' ) is not null and nullif( regionName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; ---> with i as ( select * from [#_tempInteractionsTable#] with (nolock) ) , g as ( select * from [#_tempGroupsTable#] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i #correlateWith( "where" )# ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' #correlateWith( "and" )# ) as newAccounts, ( select count( distinct interactionId ) from i #correlateWith( "where" )# ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 #correlateWith( "and" )# ) as objectiveCalls, ( select count( distinct salesRepId ) from i #correlateWith( "where" )# ) as salesRepCount, ( select count( distinct interactionDetailId ) from i #correlateWith( "where" )# ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) #correlateWith( "and" )# ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) #correlateWith( "and" )# ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) #correlateWith( "and" )# ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i #correlateWith( "where" )# ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 #correlateWith( "and" )# ) as avgItemsPerCall from g ) , i3 as ( select *, case when totalCalls != 0 then round( cast( uniqueAccounts as float ) / cast ( totalCalls as float ), 3 ) else 0.0 end as uniqueAccountsPct, case when uniqueAccounts != 0 then round( cast( newAccounts as float ) / cast ( uniqueAccounts as float ), 3 ) else 0.0 end as newAccountsPct, case when totalCalls != 0 then round( cast( objectiveCalls as float ) / cast ( totalCalls as float ), 3 ) else 0.0 end as objectiveCallsPct, case when totalCalls != 0 then round( cast( totalCalls as float ) / @weeks, 1 ) else 0.0 end as callsPerWeek, @weeks as weeks, case when totalCalls != 0 then round( cast( totalCalls as float ) / @weeks / cast( salesRepCount as float ), 1) else 0.0 end as callsPerWeekPerRep from i2 ) ,i4 as ( select i3.*, 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 i3 ) select * from i4 order by groupDisplay for json auto drop table if exists [#_tempInteractionsTable#]; drop table if exists [#_tempGroupsTable#];