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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  2 THEN                       CASE Col2 WHEN  X  THEN 10 ELSE 11 END       WHEN Col1   2 THEN 2                 ELSE 0 END as Qty

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use below example of case when with multiple conditions   SELECT   id stud name    CASE     WHEN marks  lt   40 THEN  Bad      WHEN  marks  gt   40 AND       marks  lt   100  THEN  good      ELSE  best    END AS Grade FROM Result

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

Examples related to sql

Examples related to sql-server-2008