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
Is There a Nesting Limit for Correlated Subqueries in Some Versions of Oracle
Sql: Capitalize First Letter Only
Formula for Computed Column Based on Different Table's Column
Use Select Inside an Update Query
Inner Join in Update SQL for Db2
Can Parameterized Statement Stop All SQL Injection
Why Execute Stored Procedures Is Faster Than SQL Query from a Script
Split String and Take Last Element
T-Sql: How to Use Parameters in Dynamic SQL
SQL Insert into Database with Apostrophe
What Is It Exactly a Blob in a Dbms Context
Hierarchical Queries in SQL Server 2005
Passing SQL "In" Parameter List in Jasperreport
Select a Random Sample of Results from a Query Result