interactionSummary log {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776081469500_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776081469500_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776081469500_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776081469500_04132026] 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 [tmp_interactions_2458072_BRO387_1776081469500_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776081469500_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776081469500_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776081469569_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776081469569_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776081469569_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776081469569_04132026] 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 [tmp_interactions_2458072_BRO387_1776081469569_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776081469569_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776081469398_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776081469398_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776081469398_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776081469398_04132026] 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 [tmp_interactions_2458072_BRO387_1776081469398_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776081469398_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776081469569_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776081469398_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776081469517_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776081469517_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776081469517_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776081469517_04132026] 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 [tmp_interactions_2458072_BRO387_1776081469517_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776081469517_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776081469521_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776081469521_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776081469521_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776081469521_04132026] 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 [tmp_interactions_2458072_BRO387_1776081469521_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776081469521_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776081469517_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776081469517_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 07:57:52'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776081469521_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776081469521_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:40'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082479099_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776082479099_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082479099_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082479099_04132026] 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 [tmp_interactions_2458072_BRO387_1776082479099_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082479099_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:40'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082479099_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082478918_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776082478918_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082478918_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082478918_04132026] 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 [tmp_interactions_2458072_BRO387_1776082478918_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082478918_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082478918_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776082478918_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082478968_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776082478968_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082478968_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082478968_04132026] 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 [tmp_interactions_2458072_BRO387_1776082478968_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082478968_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082478991_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776082478991_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082478991_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082478991_04132026] 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 [tmp_interactions_2458072_BRO387_1776082478991_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082478991_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082478968_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082478927_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776082478927_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082478927_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082478927_04132026] 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 [tmp_interactions_2458072_BRO387_1776082478927_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082478927_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082478991_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776082478991_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:14:41'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082478927_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082568727_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776082568727_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082568727_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082568727_04132026] 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 [tmp_interactions_2458072_BRO387_1776082568727_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082568727_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082568496_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776082568496_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082568496_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082568496_04132026] 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 [tmp_interactions_2458072_BRO387_1776082568496_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082568496_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082568511_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776082568511_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082568511_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082568511_04132026] 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 [tmp_interactions_2458072_BRO387_1776082568511_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082568511_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082568496_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776082568496_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082568727_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082568511_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082568533_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776082568533_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082568533_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082568533_04132026] 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 [tmp_interactions_2458072_BRO387_1776082568533_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082568533_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776082568504_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776082568504_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776082568504_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776082568504_04132026] 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 [tmp_interactions_2458072_BRO387_1776082568504_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776082568504_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082568504_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 08:16:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776082568533_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776082568533_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104348489_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104348489_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104348489_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104348489_04132026] 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 [tmp_interactions_2458072_BRO387_1776104348489_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104348489_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104348479_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104348479_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104348479_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104348479_04132026] 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 [tmp_interactions_2458072_BRO387_1776104348479_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104348479_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104348416_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104348416_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104348416_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104348416_04132026] 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 [tmp_interactions_2458072_BRO387_1776104348416_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104348416_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104348489_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:10'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104348479_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104348479_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104348416_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104348531_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104348531_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104348531_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104348531_04132026] 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 [tmp_interactions_2458072_BRO387_1776104348531_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104348531_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104348634_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104348634_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104348634_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104348634_04132026] 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 [tmp_interactions_2458072_BRO387_1776104348634_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104348634_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104348531_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104348531_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:19:11'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104348634_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104412170_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104412170_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104412170_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104412170_04132026] 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 [tmp_interactions_2458072_BRO387_1776104412170_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104412170_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104412170_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104412087_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104412087_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104412087_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104412087_04132026] 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 [tmp_interactions_2458072_BRO387_1776104412087_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104412087_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104412087_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104412154_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104412154_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104412154_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104412154_04132026] 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 [tmp_interactions_2458072_BRO387_1776104412154_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104412154_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104412171_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104412171_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104412171_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104412171_04132026] 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 [tmp_interactions_2458072_BRO387_1776104412171_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104412171_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104412154_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104412154_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104412293_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104412293_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104412293_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104412293_04132026] 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 [tmp_interactions_2458072_BRO387_1776104412293_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104412293_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104412293_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:14'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104412171_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104412171_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104424534_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104424534_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104424534_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104424534_04132026] 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 [tmp_interactions_2458072_BRO387_1776104424534_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104424534_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104424559_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104424559_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104424559_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104424559_04132026] 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 [tmp_interactions_2458072_BRO387_1776104424559_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104424559_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104424482_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104424482_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104424482_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104424482_04132026] 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 [tmp_interactions_2458072_BRO387_1776104424482_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104424482_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104424546_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104424546_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104424546_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104424546_04132026] 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 [tmp_interactions_2458072_BRO387_1776104424546_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104424546_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104424559_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104424482_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104424546_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104424546_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104424534_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104424534_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104424649_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104424649_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104424649_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104424649_04132026] 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 [tmp_interactions_2458072_BRO387_1776104424649_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104424649_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:27'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104424649_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:37'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104435068_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104435068_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104435068_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104435068_04132026] 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 [tmp_interactions_2458072_BRO387_1776104435068_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104435068_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:37'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104435255_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104435255_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104435255_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104435255_04132026] 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 [tmp_interactions_2458072_BRO387_1776104435255_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104435255_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:37'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104435068_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:37'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104435255_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104435255_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:38'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104435257_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104435257_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104435257_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104435257_04132026] 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 [tmp_interactions_2458072_BRO387_1776104435257_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104435257_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:38'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104435257_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104435257_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:38'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104435396_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104435396_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104435396_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104435396_04132026] 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 [tmp_interactions_2458072_BRO387_1776104435396_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104435396_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:20:38'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104435396_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:34'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104492057_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104492057_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104492057_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104492057_04132026] 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 [tmp_interactions_2458072_BRO387_1776104492057_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104492057_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:34'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104492057_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:34'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104492124_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104492124_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104492124_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104492124_04132026] 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 [tmp_interactions_2458072_BRO387_1776104492124_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104492124_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:34'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104492124_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:35'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104492287_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104492287_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104492287_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104492287_04132026] 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 [tmp_interactions_2458072_BRO387_1776104492287_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104492287_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:35'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104492287_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:35'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104492191_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104492191_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104492191_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104492191_04132026] 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 [tmp_interactions_2458072_BRO387_1776104492191_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104492191_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:35'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104492191_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104492191_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:35'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104492102_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104492102_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104492102_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104492102_04132026] 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 [tmp_interactions_2458072_BRO387_1776104492102_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104492102_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:21:35'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104492102_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104492102_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:19'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104537231_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104537231_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104537231_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104537231_04132026] 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 [tmp_interactions_2458072_BRO387_1776104537231_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104537231_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:19'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104537231_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:19'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104537316_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104537316_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104537316_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104537316_04132026] 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 [tmp_interactions_2458072_BRO387_1776104537316_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104537316_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:19'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104537359_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104537359_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104537359_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104537359_04132026] 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 [tmp_interactions_2458072_BRO387_1776104537359_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104537359_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:19'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104537316_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:20'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104537370_04132026]; 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 , 'salesRep' as groupType , salesRepId as groupId , salesRepFirstName + ' ' + salesRepLastName as groupDisplay , 'salesRepFullName' as groupColumn into [tmp_interactions_2458072_BRO387_1776104537370_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104537370_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104537370_04132026] 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 [tmp_interactions_2458072_BRO387_1776104537370_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104537370_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:20'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104537359_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104537359_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:20'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104537370_04132026] with (nolock) ) , g as ( select * from [tmp_interactions_2458072_BRO387_1776104537370_04132026_groups] with (nolock) ) ,i2 as ( select groupType, groupId, groupDisplay, groupColumn, ( select count( distinct partnerKey ) from i where i.groupId = g.groupId ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' and i.groupId = g.groupId ) as newAccounts, ( select count( distinct interactionId ) from i where i.groupId = g.groupId ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 and i.groupId = g.groupId ) as objectiveCalls, ( select count( distinct salesRepId ) from i where i.groupId = g.groupId ) as salesRepCount, ( select count( distinct interactionDetailId ) from i where i.groupId = g.groupId ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) and i.groupId = g.groupId ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) and i.groupId = g.groupId ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) and i.groupId = g.groupId ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i where i.groupId = g.groupId ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 and i.groupId = g.groupId ) 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 -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:20'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 -- drop table if exists [tmp_interactions_2458072_BRO387_1776104537532_04132026]; 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 , 'none' as groupType , cast( null as int ) as groupId , cast( null as varchar ) as groupDisplay , cast( null as varchar ) as groupColumn into [tmp_interactions_2458072_BRO387_1776104537532_04132026] from tbl_DW_Interactions_BRO387 with (nolock) where disposition != '?' and nullif( salesRepFirstName, '' ) is not null and interactionDate between @startDate and @endDate and sourceType != 'EML'; -- alter table [tmp_interactions_2458072_BRO387_1776104537532_04132026] add constraint [PKtmp_interactions_2458072_BRO387_1776104537532_04132026] 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 [tmp_interactions_2458072_BRO387_1776104537532_04132026] i ) update i set i.itemCount = x.itemCount, i.lineNbr = x.lineNbr from [tmp_interactions_2458072_BRO387_1776104537532_04132026] i inner join x on x.interactionId = i.interactionId and x.interactionDetailId = i.interactionDetailId; -- 2026-01-01 -- 2026-04-13 {ts '2026-04-13 14:22:20'} SQL declare @startDate date = ?; -- 01/01/2026 declare @endDate date = ?; -- 04/13/2026 declare @days int = datediff( day, @startDate, @endDate ) + 1; declare @weeks float = @days / 7.0; with i as ( select * from [tmp_interactions_2458072_BRO387_1776104537532_04132026] with (nolock) ) ,i2 as ( select ( select count( distinct partnerKey ) from i ) as uniqueAccounts, ( select count( distinct partnerKey ) from i where newAccount = 'Y' ) as newAccounts, ( select count( distinct interactionId ) from i ) as totalCalls, ( select count( distinct interactionId ) from i where objectiveItem = 1 ) as objectiveCalls, ( select count( distinct salesRepId ) from i ) as salesRepCount, ( select count( distinct interactionDetailId ) from i ) as totalPresentations, ( select count(*) from i where detailDisposition in ( 'STD', 'LTO' ) ) as totalWins, ( select count(*) from i where detailDisposition in ( 'FU' ) ) as totalFollowUps, ( select count(*) from i where detailDisposition in ( 'NS' ) ) as totalLoses, coalesce( ( select round( avg( cast( callsToClose as float )), 1) from i ), 0.0 ) as avgCallsToClose, ( select round( avg( cast( itemCount as float )), 1 ) from i where lineNbr = 1 ) as avgItemsPerCall ) , 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 for json auto -- 2026-01-01 -- 2026-04-13