Thursday, April 30, 2015

Case statement Help needed

Here is one way

SELECT
        P_ID,
        CASE
                WHEN COUNT(DISTINCT LineOfBusiness) = 1 AND MAX(LineOfBusiness) = 'Commercial' THEN 'Commercial'
                WHEN COUNT(DISTINCT LineOfBusiness) = 1 AND MAX(LineOfBusiness) = 'Non Commercial' THEN 'Non Commercial'
                ELSE 'Commercial & Non Commerical'
        END AS LineOfBusiness
FROM
        YourTable
GROUP BY
        P_ID;

If you don't expect the data to be used where the collations may be different, it perhaps could be simplified to something like shown below (but I would prefer my first query because it is unambiguous regardless of collation)

SELECT
        P_ID,
        CASE
                WHEN MAX(LineOfBusiness) = 'Commercial' THEN 'Commercial'
                WHEN Min(LineOfBusiness) = 'Non Commercial' THEN 'Non Commercial'
                ELSE 'Commercial & Non Commerical'
        END AS LineOfBusiness
FROM
        YourTable
GROUP BY
        P_ID;

No comments:

Post a Comment