CODEWITHSUNDEEP
sqlsql-server
Josh
Josh

Reputation: 125

SQL Server Nested Case when within select

The below case is currently part of a select query. Essentially it says if FreeStockQuantity is 0 then use the value from another field unless that is 0 or null. It then returns the result as 'Quantity'

CASE WHEN FreeStockQuantity <= 0 AND (AnalysisCode19 > 0 OR AnalysisCode19 is NOT NULL) THEN AnalysisCode19
ELSE FreeStockQuantity
END AS Quantity,

I need to somehow nest that within a case that says when the result is > 30 THEN 30.

What is the best way to do this? Ideally, I'd like to keep that ELSE in there for any unexpected results which is why I want to nest it within another case.

Upvotes: 1

Views: 142

Answers (3)

SteveC
SteveC

Reputation: 6015

select case when cwq.CalcQuantity>30 then 30 else cwq.CalcQuantity as Quantity
FROM
  (SELECT CASE WHEN (FreeStockQuantity <= 0 AND AnalysisCode19 is NOT NULL) 
               THEN AnalysisCode19 ELSE FreeStockQuantity END CalcQuantity) cwq

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

First, you don't need the NOT NULL comparison, so you can simplify the logic to:

(CASE WHEN FreeStockQuantity <= 0 AND AnalysisCode19 > 0
      THEN AnalysisCode19 ELSE FreeStockQuantity
 END) AS Quantity,

Possibly the simplest method is to just add CASE conditions:

(CASE WHEN FreeStockQuantity <= 0 AND AnalysisCode19 > 0 AND AnalysisCode19 <= 30
      THEN AnalysisCode19
      WHEN (FreeStockQuantity > 0 OR AnalysisCode19 <= 0 OR AnalysisCode19 IS NULL) AND FreeStockQuantity <= 30
      THEN FreeStockQuantity
      ELSE 30
 END)

The second method uses CROSS APPLY in the FROM clause;

SELECT . . .
       (CASE WHEN raw_quantity < 30 THEN raw_quantity ELSE 30 END)
FROM . . . CROSS APPLY
     (VALUES (CASE WHEN FreeStockQuantity <= 0 AND AnalysisCode19 > 0
                   THEN AnalysisCode19 ELSE FreeStockQuantity
              END)
     ) v(raw_quantity)

Upvotes: 1

GSerg
GSerg

Reputation: 78185

CASE WHEN FreeStockQuantity <= 0 AND (AnalysisCode19 > 0 OR AnalysisCode19 is NOT NULL) THEN
  case when AnalysisCode19 > 30 then 30 else AnalysisCode19 end
ELSE
  case when FreeStockQuantity > 30 then 30 else FreeStockQuantity end
END

Upvotes: 1

Related Questions