with clients as ( select 'BRO:' + cast( bro.broker_id as varchar ) as pkId, bro.broker_id as companyId, bro.broker_name as companyName, 'BRO' as companyType, ( select count(*) from tbl_SYS_AnomalyStatus where ownerType = 'BRO' and ownerId = bro.broker_id and hidden = 'Y' ) hiddenReportCount from tblBrokers bro with (nolock) inner join tbl_LIB_AccountSettings las with (nolock) on las.ownerType = 'BRO' and las.ownerId = bro.broker_id where 1 = 1 and bro.mdmDashboardEnabled = and las.active = 'Y' union select 'MFR:' + cast( mfr.mfr_id as varchar ) as pkId, mfr.mfr_id as companyId, mfr.mfr_name as companyName, 'MFR' as companyType, ( select count(*) from tbl_SYS_AnomalyStatus where ownerType = 'MFR' and ownerId = mfr.mfr_id and hidden = 'Y' ) hiddenReportCount from tblManufacturers mfr with (nolock) inner join tbl_LIB_AccountSettings las with (nolock) on las.ownerType = 'MFR' and las.ownerId = mfr.mfr_id where 1 = 1 and mfr.mdmDashboardEnabled = and las.active = 'Y' ), clientWithHiddenReports as ( select * from clients where hiddenReportCount < ) #forJSON( 'clients', attributes.datastore, 'companyName' )#