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

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