Simplify Nested Case When Statement

Simplify nested case when statement

Try this

SELECT CASE 
WHEN edition = 'STAN' THEN 
     CASE 
          WHEN has9 = 1 THEN '9'
          WHEN has8 = 1 THEN '8'
          WHEN has7 = 1 THEN '7'
          WHEN hasOLD = 1 THEN 'OLD'
     END
WHEN edition = 'SUI' THEN
     CASE 
          WHEN has9 = 1 THEN 'S9'
          WHEN has8 = 1 THEN 'S8'
     END
ELSE 'S7' END AS version

Best way to do nested case statement logic in SQL Server

You could try some sort of COALESCE trick, eg:

SELECT COALESCE(
  CASE WHEN condition1 THEN calculation1 ELSE NULL END,
  CASE WHEN condition2 THEN calculation2 ELSE NULL END,
  etc...
)

Nested Case Statement - Pulling the wrong value

This is your CASE simplified:

CASE   
   WHEN A <> '' OR B <> ''
      THEN NULL                                                                                    
   WHEN C <> ''
      THEN CASE    
              WHEN MM <> '' THEN MM
              WHEN NN IS NOT NULL THEN XX                                                     
              WHEN NN IS NULL THEN Concat(Upper(YY), ' ', Upper(XX))     
           END
   WHEN D <> '' OR E <> ''
      THEN Concat(Upper(XX), ' ', Upper(YY))          
   ELSE ' '
END

Seems to match your logic ...

GROUP BY with nested case expression - is there better way?

When the values are calculated directly from the row I tend to use cross apply for this as it is more concise than adding a derived table/CTE whose only purpose is to define a column alias but still needs to project out the remaining columns and contain a FROM.

This is not an option for expressions that reference window functions or aggregate functions but will work fine here.

select mp.professionals
    ,ca.age -- straight forward
    ,case ca.age -- nested case
        when 'New' then sum(fees) * 0.5
        when 'Old' then sum(fees) * 0.25
        else 0
    end Credit
    ,case ca.age -- nested case
        when 'New' then 'Welcome!'
        when 'Old' then 'Thank you for being a long-time Client!'
    end Greeting
from mattersprofessionals mp
inner join matters m on m.matters = mp.matters
inner join stmnledger sl on sl.matters = mp.matters
cross apply (select case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end) ca(age)
group by mp.professionals, ca.age

Nested case with multiple sub conditions

I might suggest that you make two small modifications to your output:

• Instead of "As", just say "A".
• Instead of "AllOK", just leave the field blank.

With these modifications, the rules are pretty easy:

select t.*,
       ((case when a1 is null or a2 is null or a3 is null then 'A' else '' end) +
        (case when b1 is null then 'B' else '' end) +
        (case when c1 is null then 'C' else '' end) +
        (case when d1 is null then 'D' else '' end)
       ) as StatusMissing
from table t;

If you do want your version, a subquery is perhaps the easiest way:

select t. . . .,
       (case when StatusMissing = '' then 'AllOK'
             when StatusMissing = 'A' then 'As'
             else StatusMissing
        end) as StatusMissing
from (select t.*,
             ((case when a1 is null or a2 is null or a3 is null then 'A' else '' end) +
              (case when b1 is null then 'B' else '' end) +
              (case when c1 is null then 'C' else '' end) +
              (case when d1 is null then 'D' else '' end)
             ) as StatusMissing
      from table t
     ) t

Nested Case Expressions In SELECT

Move the field into the case

   refID = CASE 
                WHEN externalID IS NOT NULL THEN 
                     CASE 
                          WHEN internalType like '%B1%' THEN 'C1' + externalID
                          WHEN internalType like '%B2%' THEN 'C2' + externalID
                          ELSE 'Ext'
                     END
                ELSE 'N/A'
           END  

---

Related Topics