CODEWITHSUNDEEP
sql-server
Brian Battles
Brian Battles

Reputation: 1011

Case statement for different fields

How could this be written?

I need to check a few fields for various conditions to determine whether to mark a record Y or N for keeping it. (I'm trying to exclude some records from a report where certain criteria are not met.)

I have tables aliased as ES, CQI, NA and CC in this example. So I need something like

CASE 
          WHEN SD.CType = N'ALT' AND ES.SERVICE = N'Alerts' THEN 'N'
          WHEN SD.CType = N'ALT' AND ES.SERVICE <> N'Alerts' THEN 'Y' 
          WHEN SD.CType <> N'ALT' AND ES.SERVICE <> N'Alerts' THEN 'Y' 
          WHEN SD.CType = 'BPY' AND CQI.PTYPE = 'BP' THEN 'Y'
          WHEN SD.CType <> 'BPY' AND CQI.PTYPE = 'BP' THEN 'N'
          WHEN SD.CType <> 'BPY' AND NA.BILL = 'Y' THEN 'Y' 
          WHEN SD.CType = 'BPY' AND NA.BILL = 'Y' THEN 'N'    
          WHEN SD.CType = 'LEN' AND CC.PTYPE <> 'CC' THEN 'Y'
          WHEN SD.CType = 'LEN' AND CC.PTYPE = 'CC' THEN 'N'
          ELSE 'Y'
       END    
          AS Keep

Basically these all include the SD.CType value, and then they get combined with values in 3 other fields. I could write a SELECT CASE statement in VB to do this, but I need this to run in SQL.

I appreciate any suggestions..thanks!

Upvotes: 1

Views: 901

Answers (1)

NonProgrammer
NonProgrammer

Reputation: 1387

Are you getting an error while running this? If yes, we need to see error and your query. Also, since you've defined "Else 'Y' ", you do not need to add any logic in your case statement to identify 'Y'.

Example:

CASE 
      WHEN SD.CType = N'ALT' AND ES.SERVICE = N'Alerts' THEN 'N'
      WHEN SD.CType <> 'BPY' AND CQI.PTYPE = 'BP' THEN 'N'
      WHEN SD.CType = 'BPY' AND NA.BILL = 'Y' THEN 'N'    
      WHEN SD.CType = 'LEN' AND CC.PTYPE = 'CC' THEN 'N'
      ELSE 'Y'
   END    
      AS Keep

Upvotes: 3

Related Questions