declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @partnerId int = ; declare @partnerType varchar(3) = ; with data as ( select distinct bsr.fspro_userId as salesRepId, bsr.firstName as salesRepFirstName, bsr.lastName salesRepLastName, bsr.firstName + ' ' + bsr.lastName as salesRepFullName, bsr.email salesRepEmail from tbl_CRM_Interactions i with (nolock) inner join tbl_FSpro_members bsr with (nolock) on bsr.ownerId > 0 and bsr.fspro_userId = i.dispositionUserId where i.ownerId = @ownerId and i.ownerType = @ownerType and i.partnerId = @partnerId and i.partnerType = @partnerType ) select * from data order by salesRepFullName for json auto declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @partnerId int = ; declare @partnerType varchar(3) = ; with data as ( select mfr.mfr_id, mfr.mfr_name from tbl_CRM_Interactions i with (nolock) inner join tbl_CRM_InteractionSaleDetails isd on isd.interactionId = i.interactionId and isd.ownerType =i.ownerType and isd.ownerId = i.ownerId inner join tbl_PRD_Skus s on s.ownerId = isd.ownerId and s.fsl_tablecode = isd.ownerType and s.skuId = isd.saleSubjectId and 'SKU' = isd.saleSubjectType inner join tblManufacturers mfr on mfr.mfr_id = s.mfrId where i.ownerId = @ownerId and i.ownerType = @ownerType and i.partnerId = @partnerId and i.partnerType = @partnerType union select mfr.mfr_id, mfr.mfr_name from tbl_CRM_Interactions i with (nolock) inner join tbl_CRM_InteractionSaleDetails isd on isd.interactionId = i.interactionId and isd.ownerType =i.ownerType and isd.ownerId = i.ownerId inner join tbl_PRD_Products s on s.ownerId = isd.ownerId and s.fsl_tablecode = isd.ownerType and s.prodId = isd.saleSubjectId and 'PRD' = isd.saleSubjectType inner join tblManufacturers mfr on mfr.mfr_id = s.mfrId where i.ownerId = @ownerId and i.ownerType = @ownerType and i.partnerId = @partnerId and i.partnerType = @partnerType ) select * from data order by mfr_name for json auto