How to Do Multiple Case When Conditions Using SQL Server 2008

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

There are three 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

SQL Server CASE with multiple conditions

Try this:

case 
when column_a = 'test'
and (column_b is null
or
(column_b is not null and column_c = column_d))
or column_e >=480
then 'OK'

or show some sample data and expected outcome so we can get a better understanding of what its doing

Better way to write Case When Multiple conditions

use in

SELECT    
Company_0.CompanyID
,Company_0.CoaCompanyName
,(CASE
WHEN Company_0.CompanyID in( 7942127,7950986,7955733,7955922 ) THEN 'BLUE'
WHEN Company_0.CompanyID in( 7956194,9166261 ) THEN 'RED'
WHEN Company_0.CompanyID in( 9167003,9167015 ) THEN 'YELLOW'

ELSE NULL
END' AS 'CELL'

How to put three conditions in CASE STATEMENT of SQL?


SELECT *,
CASE WHEN ETA>10 THEN 'Eligible'
WHEN Class = 'EOL' THEN 'Consult Plz'
ELSE 'Not Applicable' END AS Eligible
FROM XYZtable

Using CASE in WHERE Statement when parameter has multiple values

If your @ServiceActivity is something like 1,2,3

You can do something like this

WHERE  `,1,2,3,` LIKE `%,1,%` 

So you format your variables

WHERE  ',' + @ServiceActivity + ',' LIKE '%,' + ID + ',%'

SQL FIDDLE DEMO

SELECT *
FROM
(SELECT '1,2,3,4' as X UNION ALL
SELECT '2,3,4,5' as X UNION ALL
SELECT '3,4,5,6' as X UNION ALL
SELECT '1,3,4,5' as X
) as T
WHERE ',' + X + ',' LIKE '%,1,%'

For Your Case

(CASE WHEN @YESNOActivity = 'Yes' 
THEN ',' + @ServiceActivity + ','
ELSE NULL
END)
LIKE
(CASE WHEN @YESNOActivity = 'Yes'
THEN '%,' + TblActivity.ActivityServActId + ',%'
ELSE 0
END)

Using multiple case statements in select query

There are two ways to write case statements, you seem to be using a combination of the two

case a.updatedDate
when 1760 then 'Entered on' + a.updatedDate
when 1710 then 'Viewed on' + a.updatedDate
else 'Last Updated on' + a.updateDate
end

or

case 
when a.updatedDate = 1760 then 'Entered on' + a.updatedDate
when a.updatedDate = 1710 then 'Viewed on' + a.updatedDate
else 'Last Updated on' + a.updateDate
end

are equivalent. They may not work because you may need to convert date types to varchars to append them to other varchars.



Related Topics



Leave a reply



Submit