compare2

DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';

SELECT 'a' + CONVERT(nvarchar, B.risk_rating) AS cat,
    (CASE WHEN (SELECT COUNT(DISTINCT orderfindings.prnt_id) AS stat
        FROM orderheader, orderaudits, orderfindings
        WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
        AND orderheader.id = @id AND risk_rating = B.risk_rating AND orderaudits.Investor_Name LIKE '%' + @invest + '%') >0 OR
    (SELECT COUNT(DISTINCT orderagencies.prnt_id) AS stat
        FROM orderheader, orderaudits, orderagencies
        WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderagencies.prnt_id
        AND orderheader.id = @id AND risk_rating = B.risk_rating AND orderaudits.Investor_Name LIKE '%' + @invest + '%') > 0
    THEN 1 ELSE 0 END) AS stat

    FROM orderheader A, orderaudits B
    WHERE A.id = B.orderheader_id
    AND A.id = @id AND risk_rating > 0 AND B.Investor_Name LIKE '%' + @invest + '%'
    GROUP BY B.risk_rating