emailCampaignTargetRecipients log
{ts '2024-11-14 14:52:43'} SQL
declare @ownerId int = 2358 ;
    declare @ownerType varchar(3)  = 'BRO' ;
    declare @campaignId int = 22688 ;



	
	with targets as (

	select distinct l.ownerId, l.ownerType, l.partnerType, m.orgId as partnerId, m.firstName, m.lastName, m.fspro_userId, m.email 
	from tbl_EML_Campaign c with (nolock)
	inner join tbl_LST_PartnerLists l with (nolock)
	on l.listId = c.targetListId
	and l.ownerId = c.ownerId
	and l.ownerType = c.fsl_tablecode
	inner join tbl_LST_PartnerListItems pli with (nolock)
	on pli.listId = l.listId
	inner join tbl_LST_PartnerListItemDetails pld with (nolock)
	on pld.listId = pli.listId
	and pld.partnerId = pli.partnerId
	inner join tbl_fspro_members m with (nolock)
	on m.ownerId = c.ownerId
	and m.fsl_tablecode = c.fsl_tablecode
	and m.fspro_userId = pld.detailId
	and m.orgId = pld.partnerId
	where c.campaignId = @campaignId
	and c.ownerId = @ownerId
	and c.fsl_tablecode = @ownerType

	)

, targetsWithPartners as (


	select t.*, opr.companyName as organizationName
	  from targets t with (nolock)
	  inner join tbl_OPR_CLientOperators opr with (nolock)
	     on opr.ownerId = t.ownerId
		 and opr.fsltablecode = t.ownerType
		 and opr.operatorId = t.partnerId
	where t.partnerType = 'OPR'

	union all

	select t.*, cdr.cdr_dstName as organizationName
	  from targets t with (nolock)
	  inner join tbl_CDR_Distributors cdr with (nolock)
	     on cdr.ownerId = t.ownerId
		 and cdr.fsl_tablecode = t.ownerType
		 and cdr.cdr_recordId = t.partnerId
	where t.partnerType = 'CDR'

)


, data as (
select fspro_userId, coalesce( nullif( firstName, '' ), 'blank' ) as firstName, coalesce( nullif( lastName, '' ), 'blank' ) lastName, 
coalesce( nullif( email, '' ), 'No Email Listed' ) as emailAddress, organizationName 
from targetsWithPartners
)


    


, 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 '2024-11-14 14:53:32'} SQL
declare @ownerId int = 2358 ;
    declare @ownerType varchar(3)  = 'BRO' ;
    declare @campaignId int = 22688 ;



	
	with targets as (

	select distinct l.ownerId, l.ownerType, l.partnerType, m.orgId as partnerId, m.firstName, m.lastName, m.fspro_userId, m.email 
	from tbl_EML_Campaign c with (nolock)
	inner join tbl_LST_PartnerLists l with (nolock)
	on l.listId = c.targetListId
	and l.ownerId = c.ownerId
	and l.ownerType = c.fsl_tablecode
	inner join tbl_LST_PartnerListItems pli with (nolock)
	on pli.listId = l.listId
	inner join tbl_LST_PartnerListItemDetails pld with (nolock)
	on pld.listId = pli.listId
	and pld.partnerId = pli.partnerId
	inner join tbl_fspro_members m with (nolock)
	on m.ownerId = c.ownerId
	and m.fsl_tablecode = c.fsl_tablecode
	and m.fspro_userId = pld.detailId
	and m.orgId = pld.partnerId
	where c.campaignId = @campaignId
	and c.ownerId = @ownerId
	and c.fsl_tablecode = @ownerType

	)

, targetsWithPartners as (


	select t.*, opr.companyName as organizationName
	  from targets t with (nolock)
	  inner join tbl_OPR_CLientOperators opr with (nolock)
	     on opr.ownerId = t.ownerId
		 and opr.fsltablecode = t.ownerType
		 and opr.operatorId = t.partnerId
	where t.partnerType = 'OPR'

	union all

	select t.*, cdr.cdr_dstName as organizationName
	  from targets t with (nolock)
	  inner join tbl_CDR_Distributors cdr with (nolock)
	     on cdr.ownerId = t.ownerId
		 and cdr.fsl_tablecode = t.ownerType
		 and cdr.cdr_recordId = t.partnerId
	where t.partnerType = 'CDR'

)


, data as (
select fspro_userId, coalesce( nullif( firstName, '' ), 'blank' ) as firstName, coalesce( nullif( lastName, '' ), 'blank' ) lastName, 
coalesce( nullif( email, '' ), 'No Email Listed' ) as emailAddress, organizationName 
from targetsWithPartners
)


    


, 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 '2024-11-14 14:53:34'} SQL
declare @ownerId int = 2358 ;
    declare @ownerType varchar(3)  = 'BRO' ;
    declare @campaignId int = 22688 ;



	
	with targets as (

	select distinct l.ownerId, l.ownerType, l.partnerType, m.orgId as partnerId, m.firstName, m.lastName, m.fspro_userId, m.email 
	from tbl_EML_Campaign c with (nolock)
	inner join tbl_LST_PartnerLists l with (nolock)
	on l.listId = c.targetListId
	and l.ownerId = c.ownerId
	and l.ownerType = c.fsl_tablecode
	inner join tbl_LST_PartnerListItems pli with (nolock)
	on pli.listId = l.listId
	inner join tbl_LST_PartnerListItemDetails pld with (nolock)
	on pld.listId = pli.listId
	and pld.partnerId = pli.partnerId
	inner join tbl_fspro_members m with (nolock)
	on m.ownerId = c.ownerId
	and m.fsl_tablecode = c.fsl_tablecode
	and m.fspro_userId = pld.detailId
	and m.orgId = pld.partnerId
	where c.campaignId = @campaignId
	and c.ownerId = @ownerId
	and c.fsl_tablecode = @ownerType

	)

, targetsWithPartners as (


	select t.*, opr.companyName as organizationName
	  from targets t with (nolock)
	  inner join tbl_OPR_CLientOperators opr with (nolock)
	     on opr.ownerId = t.ownerId
		 and opr.fsltablecode = t.ownerType
		 and opr.operatorId = t.partnerId
	where t.partnerType = 'OPR'

	union all

	select t.*, cdr.cdr_dstName as organizationName
	  from targets t with (nolock)
	  inner join tbl_CDR_Distributors cdr with (nolock)
	     on cdr.ownerId = t.ownerId
		 and cdr.fsl_tablecode = t.ownerType
		 and cdr.cdr_recordId = t.partnerId
	where t.partnerType = 'CDR'

)


, data as (
select fspro_userId, coalesce( nullif( firstName, '' ), 'blank' ) as firstName, coalesce( nullif( lastName, '' ), 'blank' ) lastName, 
coalesce( nullif( email, '' ), 'No Email Listed' ) as emailAddress, organizationName 
from targetsWithPartners
)


    


, 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