emailCampaignHyperlinks log
{ts '2025-02-07 15:23:21'} SQL
declare @campaignId int = 22856 ;


    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

    ),

	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

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.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-02-07 15:23:36'} SQL
declare @campaignId int = 22856 ;


    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

    ),

	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

          from tbl_EML_CampaignHyperlinks e with (nolock)
          left outer join clickCounts c
             on c.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