Profile

Monday, 11 July 2011

SQL: Combine CASE WHEN with AND

CASE WHEN expression can combine with AND, OR..
In the following example, lets assume a condition that we need to apply a integer numeric value in three specific colors of the product AND their list price are more than 5 dollars. Using CASE WHEN on color and AND list price >5 which results only products are black, silver, red and higher than 5 dollars list price its color data value will convert as 1. 

USE AdventureWorks
GO
SELECT
Name,
ListPrice,
CASE WHEN ( Color IN ('BLACK', 'SILVER', 'RED')
        AND (ListPrice > 5))
    THEN 1 ELSE 0 END as colorcode
    FROM Production.Product


No comments: