interactionSummary log
{ts '2026-04-17 10:51:37'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776437473721_04172026];


    
     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_1776437473721_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
            and salesRepId in ( 775150  )
        
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776437473721_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776437473721_04172026] 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_1776437473721_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776437473721_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 10:51:37'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776437473721_04172026] 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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 10:51:37'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776437473434_04172026];


    
     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_1776437473434_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
        and interactionMethod in ( 'EMAIL'  )
        
			and
			
			
				segmentPath like ? 
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776437473434_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776437473434_04172026] 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_1776437473434_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776437473434_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31
-- Full-Service Restaurants/Casual Dining%

{ts '2026-04-17 10:51:38'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776437473434_04172026] 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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 10:51:38'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776437473742_04172026];


    
     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_1776437473742_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
        and interactionMethod in ( 'EMAIL'  )
        
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776437473742_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776437473742_04172026] 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_1776437473742_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776437473742_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 10:51:39'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776437473742_04172026] with (nolock)
	)

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776437473742_04172026_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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 10:51:44'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776437474074_04172026];


    
     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_1776437474074_04172026]
        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_1776437474074_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776437474074_04172026] 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_1776437474074_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776437474074_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2026-01-01
-- 2026-04-17

{ts '2026-04-17 10:51:45'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776437473775_04172026];


    
     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_1776437473775_04172026]
        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_1776437473775_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776437473775_04172026] 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_1776437473775_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776437473775_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2026-01-01
-- 2026-04-17

{ts '2026-04-17 10:51:49'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776437474074_04172026] 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-17

{ts '2026-04-17 10:51:50'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776437473775_04172026] with (nolock)
	)

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776437473775_04172026_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-17

{ts '2026-04-17 11:05:15'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776438293298_04172026];


    
     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_1776438293298_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
            and salesRepId in ( 775150  )
        
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776438293298_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776438293298_04172026] 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_1776438293298_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776438293298_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 11:05:16'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776438293298_04172026] 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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 11:05:16'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776438293227_04172026];


    
     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_1776438293227_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
        and interactionMethod in ( 'EMAIL'  )
        
			and
			
			
				segmentPath like ? 
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776438293227_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776438293227_04172026] 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_1776438293227_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776438293227_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31
-- Full-Service Restaurants/Casual Dining%

{ts '2026-04-17 11:05:16'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776438293227_04172026] 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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 11:05:17'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776438293233_04172026];


    
     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_1776438293233_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
        and interactionMethod in ( 'EMAIL'  )
        
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776438293233_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776438293233_04172026] 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_1776438293233_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776438293233_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 11:05:18'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776438293233_04172026] with (nolock)
	)

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776438293233_04172026_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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 11:05:19'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776438293444_04172026];


    
     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_1776438293444_04172026]
        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_1776438293444_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776438293444_04172026] 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_1776438293444_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776438293444_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2026-01-01
-- 2026-04-17

{ts '2026-04-17 11:05:21'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776438293444_04172026] 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-17

{ts '2026-04-17 11:05:22'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776438293259_04172026];


    
     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_1776438293259_04172026]
        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_1776438293259_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776438293259_04172026] 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_1776438293259_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776438293259_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2026-01-01
-- 2026-04-17

{ts '2026-04-17 11:05:23'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776438293259_04172026] with (nolock)
	)

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776438293259_04172026_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-17

{ts '2026-04-17 15:07:32'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776452833490_04172026];


    
     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_1776452833490_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
            and salesRepId in ( 775150  )
        
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776452833490_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776452833490_04172026] 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_1776452833490_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776452833490_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 15:07:32'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776452833490_04172026] 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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 15:07:32'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776452833360_04172026];


    
     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_1776452833360_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
        and interactionMethod in ( 'EMAIL'  )
        
			and
			
			
				segmentPath like ? 
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776452833360_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776452833360_04172026] 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_1776452833360_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776452833360_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31
-- Full-Service Restaurants/Casual Dining%

{ts '2026-04-17 15:07:32'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776452833360_04172026] 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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 15:07:33'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776452833465_04172026];


    
     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_1776452833465_04172026]
        from tbl_DW_Interactions_BRO387 with (nolock)
        where disposition != '?'

		
		
        and interactionMethod in ( 'EMAIL'  )
        
        and nullif( salesRepFirstName, '' ) is not null
        
        and interactionDate between @startDate and @endDate
        and sourceType != 'EML';

    -- alter table [tmp_interactions_2458072_BRO387_1776452833465_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776452833465_04172026] 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_1776452833465_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776452833465_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 15:07:34'} SQL
declare @startDate date = ?; -- 01/01/2025
    declare @endDate date = ?; -- 12/31/2025

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776452833465_04172026] with (nolock)
	)

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776452833465_04172026_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
-- 2025-01-01
-- 2025-12-31

{ts '2026-04-17 15:07:35'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776452833651_04172026];


    
     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_1776452833651_04172026]
        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_1776452833651_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776452833651_04172026] 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_1776452833651_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776452833651_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2026-01-01
-- 2026-04-17

{ts '2026-04-17 15:07:37'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776452833651_04172026] 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-17

{ts '2026-04-17 15:07:37'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776452833500_04172026];


    
     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_1776452833500_04172026]
        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_1776452833500_04172026] add constraint [PKtmp_interactions_2458072_BRO387_1776452833500_04172026] 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_1776452833500_04172026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776452833500_04172026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2026-01-01
-- 2026-04-17

{ts '2026-04-17 15:07:39'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/17/2026

    declare @days int = datediff( day, @startDate, @endDate ) + 1;
    declare @weeks float = @days / 7.0;

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776452833500_04172026] with (nolock)
	)

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776452833500_04172026_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-17