declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @cdr_recordId int = ; with direct as ( select 1 as displayRank, dst.dstId, cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstCode, 'Direct' as accountRole, cdr.cdr_dstCompanyType, cast( null as integer ) redist_cdr_recordId from tbl_CDR_Distributors cdr with (nolock) left outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where cdr.cdr_recordId = @cdr_recordId and cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_dstCompanyType ='B' and cdr.fsl_mapPriority = 1 ), redist as ( select 2 as displayName, dst.dstId, cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstCode, 'Redist for ' + rcdr.cdr_dstName as accountRole, cdr.cdr_dstCompanyType, rcdr.cdr_recordId redist_cdr_recordId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId inner join tbl_CDR_Distributors rcdr with (nolock) on rcdr.ownerId = cdr.ownerId and rcdr.fsl_tablecode = cdr.fsl_tablecode and rcdr.cdr_recordId = cdr.cdr_redistAcctFor where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_dstCompanyType = 'S' and cdr.fsl_dstId = ( select dstId from direct ) and cdr.cdr_specialAcctType = 'RDA' ), distributorAccounts as ( select * from direct union all select * from redist ) #forJSON( 'distributorAccounts', attributes.dataStore, 'displayRank' )#