emailCampaignHyperlinkDetails log
{ts '2025-05-06 15:06:15'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3)  = 'BRO' ;
    declare @campaignId int = 22933 ;
    declare @hyperlink varchar(1000) = 'https://dev.fsenablers.com/fse/salesteamportal/affinitygroup/public/form.cfm?token=QTRFNTQ1MEQ4MjA0NjkwRkU0NjY3OUE4QTUxNDU1MzZFNzExNzhCNUQ5OTlEMDIzNTdFOUFCQ0MxQkEyOTNCQw%3D%3D&formId=100&linkType=EMR&linkId=0&' ;

	  WITH clicksPerUser AS (
		SELECT
			rec.FSL_TableCode
			,rec.ownerId
			,rec.recipientid /*note: this is not FSPro_UserId*/
			,rec.token
			,rec.campaignid
			,clk.ClickDestination
			,rec.FirstName
			,rec.LastName
			,rec.Organization
			,rec.EmailAddress
		FROM tbl_EML_CampaignRecipients AS rec WITH (nolock)
		INNER JOIN tbl_EML_RecipientClicks AS clk WITH (nolock)
			ON rec.recipientId = clk.recipientId
			AND rec.token = clk.token
			AND clk.ignoreClick <> 'Y'
		WHERE rec.campaignID = @campaignId
	),

	responsesPerUser as (

      select c.campaignId, r.recipientId, l.hyperlink, count(*) responseCount, s.formId, max( s.submissionId ) submissionId
      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
		and l.hyperlink = @hyperlink

      group by c.campaignId, r.recipientId, l.hyperlink, s.formId


	),

  rawData as (
	SELECT
		clicksPerUser.recipientid as recipientid, /*note: this is not FSPro_UserId*/
		COALESCE(clicksPerUser.token, 0) as token,
		COALESCE(cmp.campaignid, 0) as campaignId,
		lnk.hyperlink as hyperlink,
		COALESCE(MAX(clicksPerUser.FirstName), '') as firstName,
		COALESCE(MAX(clicksPerUser.LastName), '') as lastName,
		COALESCE(MAX(clicksPerUser.Organization), '') as organization,
		COALESCE(MAX(CASE WHEN clicksPerUser.FSL_TableCode='BRO' THEN 'Private Address' ELSE clicksPerUser.EmailAddress END), '') as emailAddress,
		COALESCE(COUNT(clicksPerUser.ClickDestination), 0) as clicks,
		COALESCE( rpu.responseCount, 0 ) as responses,
		rpu.formId,
		rpu.submissionId
	FROM tbl_EML_Campaign AS cmp WITH (nolock)
	INNER JOIN tbl_EML_CampaignHyperlinks AS lnk WITH (nolock)
		ON cmp.campaignId = lnk.campaignId
	INNER JOIN clicksPerUser 
		ON cmp.campaignId = clicksPerUser.campaignId
		AND lnk.hyperlink = clicksPerUser.clickDestination
	left outer join responsesPerUser rpu
	    on rpu.campaignId = cmp.campaignId
		and rpu.hyperlink = lnk.hyperlink
		and rpu.recipientId = clicksPerUser.recipientId
	WHERE cmp.campaignId = @campaignId
		AND cmp.FSL_TableCode = @ownerType
		AND cmp.ownerId = @ownerId
    AND lnk.hyperlink = @hyperlink
	GROUP BY
		clicksPerUser.recipientid /*note: this is not FSPro_UserId*/
		,clicksPerUser.token
		,cmp.campaignid
		,lnk.hyperlink
		,rpu.responseCount
		,rpu.formId
		,rpu.submissionId

  ),

  data as (
    select *, firstName + ' ' + lastName as fullName
      from rawData
  )

    


, 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 fullName
		
		
		 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