emailCampaignHyperlinks log
{ts '2025-05-01 15:36:23'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22029 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
{ts '2025-05-01 15:36:23'} SQL

declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22029 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2025-05-01 15:36:32'} SQL
{ts '2025-05-01 15:36:32'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22029 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22029 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER


{ts '2025-05-01 16:01:58'} SQL
{ts '2025-05-01 16:01:58'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22029 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22029 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER


{ts '2025-05-01 16:03:53'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22928 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2025-05-01 16:03:53'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22928 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2025-05-01 16:03:59'} SQL
{ts '2025-05-01 16:03:59'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 21931 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 21931 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER


{ts '2025-05-01 16:04:10'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22159 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2025-05-01 16:04:10'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22159 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2025-05-01 16:04:14'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22929 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2025-05-01 16:04:14'} SQL
declare @ownerId int = 6868 ;
    declare @ownerType varchar(3)  = 'MFR' ;

    declare @campaignId int = 22929 ;


    with clickCounts as (

	 select h.hyperlink, count(*) as clickCount
	   from tbl_EML_CampaignRecipients r with (nolock)
	  inner join tbl_EML_CampaignHyperlinks h with (nolock)
	     on h.campaignId = r.campaignId
	  inner join tbl_EML_RecipientClicks c with (nolock)
	     on c.recipientId = r.recipientId
	    and c.clickDestination = h.hyperlink   
	  where r.campaignId = @campaignId
	    and c.ignoreClick = 'N'
      group by h.hyperlink

    ),

    responseCounts as (

      select l.hyperlink, count(*) responseCount
      from tbl_EML_Campaign c with (nolock)
      inner join tbl_EML_CampaignHyperlinks l with (nolock)
        on l.campaignId = c.campaignId
      inner join tbl_EML_CampaignRecipients r with (nolock)
          on r.campaignId = l.campaignId
      inner join tbl_WRK_FormSubmissions s with (nolock)
        on s.ownerId = c.ownerId
        and s.ownerType = c.fsl_tablecode
        and s.formId = l.formId
        and s.linkType ='EMR'
        and s.linkId = r.recipientId

      where c.campaignId = @campaignId
        and c.ownerId = @ownerId
        and c.fsl_tablecode = @ownerType

      group by l.hyperlink


    ),

	data as (
        select e.hyperlink,
               e.hyperlinkSubjectType, 
               e.hyperlinkSubjectId,
               e.insertDate,
               e.hyperlinkId,
               e.missingLink,
               c.clickCount,
               case when e.hyperlinkSubjectType = 'MFR' then e.hyperlinkSubjectId else cast( null as integer ) end as mfr_id,

               e.formId,
               case when e.formId > 0 then 'Y' else 'N' end as formLink,
               r.responseCount

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.hyperlink = e.hyperlink
          left outer join responseCounts r
             on r.hyperlink = e.hyperlink
         where e.campaignId = @campaignId
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by hyperlink
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER