emailCampaignRecipients log
{ts '2025-06-18 12:42:42'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3)  = 'BRO' ;
    declare @campaignId int = 23041 ;

	WITH unsubscribedDetails AS (
		/*1.b get details of unsubscribed*/
		SELECT
			rec.FSL_TableCode as recipientOwnerType,
			rec.ownerId as recipientOwnerId,
			rec.recipientId,  /*note: this is not FSPro_UserId*/
			rec.token,
			rec.campaignId,
			MAX(uns.unsubscribeTime) as unsubscribeDate,
			MAX(CASE WHEN uns.unsubscribeId IS NULL THEN 0 ELSE 1 END) as isUnsubscribed
		FROM tbl_EML_Campaign AS cmp WITH (nolock)
		INNER JOIN tbl_EML_CampaignRecipients AS rec WITH (nolock)
			ON cmp.campaignId = rec.campaignId
		LEFT JOIN tbl_EML_CampaignUnsubscribes AS uns WITH (nolock)
			ON rec.FSL_TableCode = uns.ownerType
			AND rec.ownerId = uns.ownerId
			AND rec.campaignId = uns.campaignId
			AND rec.recipientId = uns.recipientId
		WHERE cmp.campaignId = @campaignId
			AND cmp.FSL_TableCode = @ownerType
			AND cmp.ownerId = @ownerId
		GROUP BY
			rec.FSL_TableCode
			,rec.ownerId
			,rec.recipientId
			,rec.token
			,rec.campaignId
	),

	campaignEmails as (
		select emailId, emailTrackingCode, emailFrom, emailTo, emailDate, emailSent, emailSubject
		  from tbl_WRK_Emails with (nolock)
		 where ownerId = @ownerId
		   and ownerType = @ownerType
		   and emailTrackingCode like 'EM[_]' + cast( @campaignId as varchar ) + '[_]%'
	),

	/*1.a get details of opened emails*/
    recipients as (
	SELECT
		rec.FSL_TableCode as recipientOwnerType,
		rec.ownerId as recipientOwnerId,
		rec.recipientId, /*note: this is not FSPro_UserId*/
		rec.token,
		rec.campaignId,
		rec.FirstName as firstName,
		rec.LastName as lastName,
		rec.Organization as organizationName,
		-- CASE WHEN rec.FSL_TableCode='BRO' THEN 'Private Address' ELSE rec.EmailAddress END as emailAddress,
		rec.emailAddress,
		CASE WHEN rec.BeaconCount > 0 THEN 1 ELSE 0 END as isOpened,
		COALESCE(rec.BeaconCount, 0) as numOpens,
		uns.unsubscribeDate,
		uns.isUnsubscribed,
		'EM_' + cast( rec.campaignId as varchar ) + '_' + cast( rec.recipientId  as varchar ) as emailTrackingCode
	FROM tbl_EML_Campaign AS cmp WITH (nolock)
	INNER JOIN tbl_EML_CampaignRecipients AS rec WITH (nolock)
		ON cmp.campaignId = rec.campaignId
	LEFT JOIN unsubscribedDetails AS uns WITH (noexpand) 
		ON uns.recipientOwnerType = rec.FSL_TableCode
		AND uns.recipientOwnerId = rec.ownerId
		AND uns.recipientId = rec.recipientId
		AND uns.token = rec.token
		AND uns.campaignId = rec.campaignId
	WHERE cmp.campaignId = @campaignId
		AND cmp.FSL_TableCode = @ownerType
		AND cmp.ownerId = @ownerId
    ),

	data as (
		select r.*, e.emailId, e.emailFrom, e.emailTo, e.emailDate, e.emailSent, e.emailSubject
		  from recipients r with (nolock)
		  left outer join campaignEmails e with (nolock)
		    on e.emailTrackingCode = r.emailTrackingCode

	)


    


, 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 firstName
		
		
		 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-06-18 12:47:18'} SQL
declare @ownerId int = 387 ;
    declare @ownerType varchar(3)  = 'BRO' ;
    declare @campaignId int = 23041 ;

	WITH unsubscribedDetails AS (
		/*1.b get details of unsubscribed*/
		SELECT
			rec.FSL_TableCode as recipientOwnerType,
			rec.ownerId as recipientOwnerId,
			rec.recipientId,  /*note: this is not FSPro_UserId*/
			rec.token,
			rec.campaignId,
			MAX(uns.unsubscribeTime) as unsubscribeDate,
			MAX(CASE WHEN uns.unsubscribeId IS NULL THEN 0 ELSE 1 END) as isUnsubscribed
		FROM tbl_EML_Campaign AS cmp WITH (nolock)
		INNER JOIN tbl_EML_CampaignRecipients AS rec WITH (nolock)
			ON cmp.campaignId = rec.campaignId
		LEFT JOIN tbl_EML_CampaignUnsubscribes AS uns WITH (nolock)
			ON rec.FSL_TableCode = uns.ownerType
			AND rec.ownerId = uns.ownerId
			AND rec.campaignId = uns.campaignId
			AND rec.recipientId = uns.recipientId
		WHERE cmp.campaignId = @campaignId
			AND cmp.FSL_TableCode = @ownerType
			AND cmp.ownerId = @ownerId
		GROUP BY
			rec.FSL_TableCode
			,rec.ownerId
			,rec.recipientId
			,rec.token
			,rec.campaignId
	),

	campaignEmails as (
		select emailId, emailTrackingCode, emailFrom, emailTo, emailDate, emailSent, emailSubject
		  from tbl_WRK_Emails with (nolock)
		 where ownerId = @ownerId
		   and ownerType = @ownerType
		   and emailTrackingCode like 'EM[_]' + cast( @campaignId as varchar ) + '[_]%'
	),

	/*1.a get details of opened emails*/
    recipients as (
	SELECT
		rec.FSL_TableCode as recipientOwnerType,
		rec.ownerId as recipientOwnerId,
		rec.recipientId, /*note: this is not FSPro_UserId*/
		rec.token,
		rec.campaignId,
		rec.FirstName as firstName,
		rec.LastName as lastName,
		rec.Organization as organizationName,
		-- CASE WHEN rec.FSL_TableCode='BRO' THEN 'Private Address' ELSE rec.EmailAddress END as emailAddress,
		rec.emailAddress,
		CASE WHEN rec.BeaconCount > 0 THEN 1 ELSE 0 END as isOpened,
		COALESCE(rec.BeaconCount, 0) as numOpens,
		uns.unsubscribeDate,
		uns.isUnsubscribed,
		'EM_' + cast( rec.campaignId as varchar ) + '_' + cast( rec.recipientId  as varchar ) as emailTrackingCode
	FROM tbl_EML_Campaign AS cmp WITH (nolock)
	INNER JOIN tbl_EML_CampaignRecipients AS rec WITH (nolock)
		ON cmp.campaignId = rec.campaignId
	LEFT JOIN unsubscribedDetails AS uns WITH (noexpand) 
		ON uns.recipientOwnerType = rec.FSL_TableCode
		AND uns.recipientOwnerId = rec.ownerId
		AND uns.recipientId = rec.recipientId
		AND uns.token = rec.token
		AND uns.campaignId = rec.campaignId
	WHERE cmp.campaignId = @campaignId
		AND cmp.FSL_TableCode = @ownerType
		AND cmp.ownerId = @ownerId
    ),

	data as (
		select r.*, e.emailId, e.emailFrom, e.emailTo, e.emailDate, e.emailSent, e.emailSubject
		  from recipients r with (nolock)
		  left outer join campaignEmails e with (nolock)
		    on e.emailTrackingCode = r.emailTrackingCode

	)


    


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