Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Combining multiple condition in single case statement in Sql Server

According to the following description I have to frame a CASE...END statement in SQL server , help me to frame a complex CASE...END statement to fulfill the following condition.

if PAT_ENT.SCR_DT is not null and PAT_ENTRY.ELIGIBILITY is null then display display 'Favor'
if PAT_ENT.SCR_DT is not null and PAT_ENTRY.EL is equal to No, display 'Error'
if PAT_ENTRY.EL is Yes and DS.DES is equal to null or OFF, display 'Active'
if DS.DES is equal to N, display 'Early Term'
if DS.DES is equal to Y, display 'Complete'

Thanks in advance.

like image 365
swetha Avatar asked Jun 15 '10 07:06

swetha


People also ask

Can we put multiple condition in CASE statement?

You can evaluate multiple conditions in the CASE statement.

Can we use multiple case statements in SQL?

SQL case statement with multiple conditions is known as the Search case statement. So, You should use its syntax if you want to get the result based upon different conditions -.


1 Answers

You can put the condition after the WHEN clause, like so:

SELECT
  CASE
    WHEN PAT_ENT.SCR_DT is not null and PAT_ENTRY.ELIGIBILITY is null THEN 'Favor'
    WHEN PAT_ENT.SCR_DT is not null and PAT_ENTRY.EL = 'No' THEN 'Error'
    WHEN PAT_ENTRY.EL = 'Yes' and ISNULL(DS.DES, 'OFF') = 'OFF' THEN 'Active'
    WHEN DS.DES = 'N' THEN 'Early Term'
    WHEN DS.DES = 'Y' THEN 'Complete'
  END
FROM
  ....

Of course, the argument could be made that complex rules like this belong in your business logic layer, not in a stored procedure in the database...

like image 130
Dean Harding Avatar answered Oct 17 '22 07:10

Dean Harding