emailCampaignRecipientGroups log
{ts '2025-05-16 11:51:29'} SQL
declare @ownerId int = 2252 ;
    declare @ownerType varchar(3)  = 'MFR' ;
    declare @campaignId int = 17131 ;

    with rawData as (
        select g.groupId, g.groupName, g.groupDescription, cast( case when cgl.groupId is not null then 1 else 0 end as bit ) recipientGroup,
            coalesce( c.category, 'Uncategorized Groups' ) category, coalesce( c.sortRank, max( c.sortRank ) over () + 1  ) categorySort, coalesce( c.categoryId, 0 ) categoryId,
            case when g.modvisibility like '%22%' then 1 else 0 end emailGroup

        from tbl_SEC_Groups g with (nolock)
        left outer join tbl_EML_CampaignGroupLnk cgl with (nolock)
           on cgl.groupId = g.groupId
           and cgl.campaignId = @campaignId
        left outer join tbl_SEC_Categories c with (nolock)
            on c.categoryId = g.categoryId
            and c.ownerId = g.ownerId
            and c.fsl_tablecode = g.fsl_tablecode        
        where g.ownerId = @ownerId
            and g.fsl_tablecode = @ownerType
    ),

    data as (
        select * from rawData where recipientGroup = 1 or emailGroup = 1 
    )

    


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