with owners as ( select distinct cdr.ownerId, cdr.fsl_tablecode as ownerType from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where cdr.fsl_dstIdDate is not null and cdr.fsl_dstId > 0 and cdr.fsl_dstIdBy > 0 and convert( datetime, convert( varchar, fsl_dstIdDate, 101 )) >= and convert( datetime, convert( varchar, fsl_dstIdDate, 101 )) < ), ownersFull as ( select o.*, cho.cho_name as ownerName from tbl_CHO_Operators cho with (nolock) inner join owners o on o.ownerId = cho.cho_id and o.ownerType = 'CHO' union all select o.*, dst.dstName as ownerName from tbl_DST_Distributors dst with (nolock) inner join owners o on o.ownerId = dst.dstId and o.ownerType = 'DST' union all select o.*, mfr.mfr_name as ownerName from tblManufacturers mfr with (nolock) inner join owners o on o.ownerId = mfr.mfr_id and o.ownerType = 'MFR' union all select o.*, bro.broker_name as ownerName from tblBrokers bro with (nolock) inner join owners o on o.ownerId = bro.broker_id and o.ownerType = 'BRO' union all select o.*, msc.company as ownerName from tbl_MSC_FSLibraryUsers msc with (nolock) inner join owners o on o.ownerId = msc.userId and o.ownerType = 'MSC' ) select ownerType, ownerType + ':' + cast( ownerId as varchar ) as ownerKey, ownerName from ownersFull order by ownerType, ownerName for json auto, include_null_values