select cdr.ownerid, cdr.fsl_tablecode ownerType, 'DATA' anomalyType, 'otherDirectShipDetector' detectorName, cdr.cdr_dstname as companyName, cdr.cdr_dstname + ' (' + convert( varchar, cdr.cdr_recordId ) + ') is missing a corresponding operator' as message, 1 anomalyCount from tbl_CDR_Distributors cdr left outer join tbl_OPR_ClientOperators opr on cdr.ownerId = opr.ownerId and cdr.fsl_tablecode = opr.fsltablecode and cdr.cdr_dstcode = opr.directCustomerNbr where cdr.cdr_dstcompanytype = 'O' and cdr.ownerId > 0 and nullif( opr.directCustomerNbr, '' ) is null union select cdr.ownerid, cdr.fsl_tablecode ownerType, 'DATA' anomalyType, 'otherDirectShipDetector' detectorName, cdr.cdr_dstname as companyName, cdr.cdr_dstname + ' (' + convert( varchar, cdr.cdr_recordId ) + ') has more than one corresponding operator' as message, 1 anomalyCount from tbl_CDR_Distributors cdr inner join tbl_OPR_ClientOperators opr on cdr.ownerId = opr.ownerId and cdr.fsl_tablecode = opr.fsltablecode and cdr.cdr_dstcode = opr.directCustomerNbr where cdr.cdr_dstcompanytype = 'O' and cdr.ownerId > 0 group by cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_dstname, cdr.cdr_recordId having count( cdr.cdr_recordId ) > 1