interactionSummary log
{ts '2026-04-15 18:26:41'} SQL
{ts '2026-04-15 18:26:41'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776291988566_04152026];


    
     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_1776291988566_04152026]
        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_1776291988566_04152026] add constraint [PKtmp_interactions_2458072_BRO387_1776291988566_04152026] 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_1776291988566_04152026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776291988566_04152026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776291989060_04152026];


    
     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_1776291989060_04152026]
        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_1776291989060_04152026] add constraint [PKtmp_interactions_2458072_BRO387_1776291989060_04152026] 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_1776291989060_04152026] i
	)


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


{ts '2026-04-15 18:26:41'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776291988562_04152026];


    
     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_1776291988562_04152026]
        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_1776291988562_04152026] add constraint [PKtmp_interactions_2458072_BRO387_1776291988562_04152026] 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_1776291988562_04152026] i
	)


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

{ts '2026-04-15 18:26:41'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776291988601_04152026];


    
     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_1776291988601_04152026]
        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_1776291988601_04152026] add constraint [PKtmp_interactions_2458072_BRO387_1776291988601_04152026] 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_1776291988601_04152026] i
	)


	update i
	    set i.itemCount = x.itemCount,
		    i.lineNbr = x.lineNbr
	   from [tmp_interactions_2458072_BRO387_1776291988601_04152026] i
	   inner join x on x.interactionId = i.interactionId
	    and x.interactionDetailId = i.interactionDetailId;
-- 2026-01-01
{ts '2026-04-15 18:26:41'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

    
    -- drop table if exists [tmp_interactions_2458072_BRO387_1776291988567_04152026];


    
     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_1776291988567_04152026]
        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_1776291988567_04152026] add constraint [PKtmp_interactions_2458072_BRO387_1776291988567_04152026] 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_1776291988567_04152026] i
	)


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

-- 2026-04-15

{ts '2026-04-15 18:26:41'} SQL
{ts '2026-04-15 18:26:41'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

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

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776291989060_04152026] 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
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

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

    
	with i as (
        select * from [tmp_interactions_2458072_BRO387_1776291988566_04152026] 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-01-01
-- 2026-04-15
-- 2026-04-15


{ts '2026-04-15 18:26:41'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

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

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

{ts '2026-04-15 18:26:41'} SQL
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

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

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

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776291988601_04152026_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
{ts '2026-04-15 18:26:41'} SQL
-- 2026-01-01
declare @startDate date = ?; -- 01/01/2026
    declare @endDate date = ?; -- 04/15/2026

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

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

    
    , g as (
        select *
          from [tmp_interactions_2458072_BRO387_1776291988567_04152026_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-04-15
-- 2026-01-01

-- 2026-04-15