declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @campaignAttrTypeId int = ; declare @campaignGroupAttrTypeId int = ; declare @territoryAttrTypeId int = ; with campaignGroups as ( select attrId campaignGroupId, attrDescription as campaignGroup, ownerId, fsl_tablecode as ownerType from tbl_ORG_Attributes with (nolock) where ownerId = @ownerId and fsl_tablecode = @ownerType and attrTypeId = @campaignGroupAttrTypeId ), data as ( select c.* , cg.campaignGroup, e.name as emailCampaignName from tbl_GTM_Campaigns c with (nolock) left outer join tbl_EML_Campaign e with (nolock) on e.ownerId = c.ownerId and e.fsl_tablecode = c.ownerType and e.campaignId = c.emailCampaignId left outer join campaignGroups cg on cg.ownerId = c.ownerId and cg.ownerType = c.ownerType and cg.campaignGroupId = c.campaignGroupId where c.ownerId = @ownerId and c.ownerType = @ownerType ) , formatted as ( select d.*, d.targetListId as targetPartnerList, mu.firstName + ' ' + mu.lastName as campaignManagerDisplay, mu.email as campaignManagerEmail, su.firstName + ' ' + su.lastName as salesManagerDisplay, su.email as salesManagerEmail, uu.firstName + ' ' + uu.lastName as updatedByDisplay, cu.firstName + ' ' + cu.lastName as createdByDisplay, case when mfr.mfr_id is not null then mfr.mfr_name else 'INTERNAL' end as businessOwnerDisplay, ter.name as territoryName, ter.territoryPath, case when d.endDate >= cast( getDate() as date ) then 'Y' else 'N' end allowDeletion from data d left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = d.ownerId and ter.fsl_tablecode = d.ownerType and ter.territoryId = d.territoryId left outer join tbl_Fspro_members mu with (nolock) on mu.ownerId = d.ownerId and mu.fsl_tablecode = d.ownerType and mu.fspro_userId = d.campaignManagerUserId left outer join tbl_Fspro_members su with (nolock) on su.ownerId = d.ownerId and su.fsl_tablecode = d.ownerType and su.fspro_userId = d.salesManagerUserId left outer join tbl_Fspro_members uu with (nolock) on uu.ownerId = d.ownerId and uu.fsl_tablecode = d.ownerType and uu.fspro_userId = d.updatedBy left outer join tbl_Fspro_members cu with (nolock) on cu.ownerId = d.ownerId and cu.fsl_tablecode = d.ownerType and cu.fspro_userId = d.createdBy left outer join tblManufacturers mfr with (nolock) on mfr.mfr_id = d.businessOwnerId and 'MFR' = d.businessOwnerType ) #forJSON( 'formatted', attributes.datastore, 'campaignName' )#