Combining multiple condition in single case statem

问题:

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.

回答1:

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...

回答2:

select ROUND(CASE 

WHEN  CONVERT( float, REPLACE( isnull( value1,''),',',''))='' AND CONVERT( float, REPLACE( isnull( value2,''),',',''))='' then  CONVERT( float, REPLACE( isnull( value3,''),',',''))

WHEN  CONVERT( float, REPLACE( isnull( value1,''),',',''))='' AND CONVERT( float, REPLACE( isnull( value2,''),',',''))!='' then  CONVERT( float, REPLACE( isnull( value3,''),',',''))

WHEN  CONVERT( float, REPLACE( isnull( value1,''),',',''))!='' AND CONVERT( float, REPLACE( isnull( value2,''),',',''))='' then  CONVERT( float, REPLACE( isnull( value3,''),',',''))

else CONVERT( float, REPLACE(isnull( value1,''),',','')) end,0)  from Tablename where    ID="123"