with broEmailDomains as ( select d.ownerId, d.ownerType, d.emailDomain, d.corporate, d.ready, md.ready as mfrReady, case when exists ( select * from tblMfrBrokerLink mbl where mbl.broker_id = d.ownerId and mbl.mfr_id = md.ownerId ) then 'Y' else 'N' end as brokerSide, case when exists ( select * from tbl_SMA_BrokerOfficeLink sma where sma.brokerid = d.ownerId and sma.mfr_id = md.ownerId ) then 'Y' else 'N' end mfrSide from tbl_WRK_EmailDomains d left outer join tbl_WRK_EmailDomains md on md.ownerType = 'MFR' and md.emailDomain = d.emailDomain and md.corporate = 'Y' where d.ownerType = 'BRO' and d.ownerId = ), emailDomains as ( select emailDomain, corporate, case when mfrReady is null then ready when brokerSide = 'Y' and mfrSide = 'Y' then mfrReady else 'N' end as ready from broEmailDomains ) with mfrEmailDomains as ( select d.ownerId, d.ownerType, d.emailDomain, d.ready, d.corporate, case when exists ( select * from tblMfrBrokerLink mbl where mbl.broker_id = bd.ownerId and mbl.mfr_id = d.ownerId ) then 'Y' else 'N' end as brokerSide, case when exists ( select * from tbl_SMA_BrokerOfficeLink sma where sma.brokerid = bd.ownerId and sma.mfr_id = d.ownerId ) then 'Y' else 'N' end as mfrSide, bd.ready brokerReady from tbl_WRK_EmailDomains d with (nolock) left outer join tbl_WRK_EmailDomains bd with (nolock) on bd.ownerType = 'BRO' and bd.emailDomain = d.emailDomain and bd.corporate = 'Y' where d.ownerType = 'MFR' and d.ownerId = ), emailDomains as ( select emailDomain, corporate, case when brokerReady is null then ready when brokerSide = 'Y' and mfrSide = 'Y' then brokerReady else 'N' end as ready from mfrEmailDomains ) with emailDomains as ( select emailDomain, ready, corporate from tbl_WRK_EmailDomains ed with (nolock) where ed.ownerId = and ed.ownerType = ) #forJSON( 'emailDomains', attributes.datastore, 'emailDomain' )#