How do I do multiple CASE WHEN conditions using SQL Server 2008?

What I'm trying to do is use more than one CASE WHEN condition for the same column.

Here is my code for the query:

 SELECT Url='', p.ArtNo, p.[Description], p.Specification, CASE WHEN 1 = 1 or 1 = 1 THEN 1 ELSE 0 END as Qty, p.NetPrice, [Status] = 0 FROM Product p (NOLOCK) 

However, what I want to do is use more then one WHEN for the same column "qty".

As in the following code:

IF // CODE ELSE IF // CODE ELSE IF // CODE ELSE // CODE 

11 Answers

There are two formats of case expression. You can do CASE with many WHEN as;

CASE WHEN Col1 = 1 OR Col3 = 1 THEN 1 WHEN Col1 = 2 THEN 2 ... ELSE 0 END as Qty 

Or a Simple CASE expression

CASE Col1 WHEN 1 THEN 11 WHEN 2 THEN 21 ELSE 13 END 

Or CASE within CASE as;

CASE WHEN Col1 < 2 THEN CASE Col2 WHEN 'X' THEN 10 ELSE 11 END WHEN Col1 = 2 THEN 2 ... ELSE 0 END as Qty 

Just use this one, You have to use more when they are classes.

SELECT Url='', p.ArtNo, p.[Description], p.Specification, CASE WHEN 1 = 1 or 1 = 1 THEN 1 WHEN 2 = 2 THEN 2 WHEN 3 = 3 THEN 3 ELSE 0 END as Qty, p.NetPrice, [Status] = 0 FROM Product p (NOLOCK) 

You can use below example of case when with multiple conditions.

SELECT id,stud_name, CASE WHEN marks <= 40 THEN 'Bad' WHEN (marks >= 40 AND marks <= 100) THEN 'good' ELSE 'best' END AS Grade FROM Result 

Something Like this, Two Conditions Two Columns

SELECT ITEMSREQ.ITEM AS ITEM, ITEMSREQ.CANTIDAD AS CANTIDAD, (CASE WHEN ITEMSREQ.ITEMAPROBADO=1 THEN 'APROBADO' WHEN ITEMSREQ.ITEMAPROBADO=0 THEN 'NO APROBADO' END) AS ITEMS, (CASE WHEN ITEMSREQ.ITEMAPROBADO = 0 THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL THEN 'ITEM NO APROBADO PARA ENTREGA' END WHEN ITEMSREQ.ITEMAPROBADO = 1 THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL THEN 'ITEM AUN NO RECIBIDO' WHEN REQUISICIONES.RECIBIDA=1 THEN 'RECIBIDO' WHEN REQUISICIONES.RECIBIDA=0 THEN 'NO RECIBIDO' END END) AS RECIBIDA FROM ITEMSREQ INNER JOIN REQUISICIONES ON ITEMSREQ.CNSREQ = REQUISICIONES.CNSREQ 

This can be an efficient way of performing different tests on a single statement

select case colour_txt when 'red' then 5 when 'green' then 4 when 'orange' then 3 else 0 end as Pass_Flag 

this only works on equality comparisons!

case when a.REASONID in ('02','03','04','05','06') then case b.CALSOC when '1' then 'yes' when '2' then 'no' else 'no' end else 'no' end 

 case when first_condition then first_condition_result_true else case when second_condition then second_condition_result_true else second_condition_result_false end end end as qty 

Its just that you need multiple When for a single case to behave it like if.. Elseif else..

 Case when 1=1 //if Then When 1=1 //else if Then.... When ..... //else if Then Else //else ....... End 

I had a similar but it was dealing with dates. Query to show all items for the last month, works great without conditions until Jan. In order for it work correctly, needed to add a year and month variable

declare @yr int declare @mth int set @yr=(select case when month(getdate())=1 then YEAR(getdate())-1 else YEAR(getdate())end) set @mth=(select case when month(getdate())=1 then month(getdate())+11 else month(getdate())end) 

Now I just add the variable into condition: ...

(year(CreationTime)=@yr and MONTH(creationtime)=@mth) 

Combining all conditions

select a.* from tbl_Company a where a.Company_ID NOT IN (1,2) AND ( (0 = CASE WHEN (@Fromdate = '' or @Todate='') THEN 0 ELSE 1 END ) -- if 0=0 true , if 0=1 fails (filter only when the fromdate and todate is present) OR (a.Created_Date between @Fromdate and @Todate ) ) 

You will get an idea from this code.

SELECT (CASE WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1001' THEN 'DM' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1002' THEN 'GS' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1003' THEN 'MB' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1004' THEN 'MP' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1005' THEN 'PL' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1008' THEN 'DM-27' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1011' THEN 'PB' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1012' THEN 'UT-2' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1013' THEN 'JGC' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1014' THEN 'SB' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1015' THEN 'IR' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1016' THEN 'UT-3' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1017' THEN 'UT-4' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1019' THEN 'KR' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1020' THEN 'SYB-SB' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1021' THEN 'GR' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1022' THEN 'SYB-KP' WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1026' THEN 'BNS' ELSE '' END) AS OUTLET FROM matrixcrm.Transact