Best Way to Do Nested Case Statement Logic in SQL Server

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

How can I write nested CASE statement based on query below?

Use a nested CASE expression

SELECT 
    CASE 
        WHEN ISNULL(grouptt.controlno, 0) <> 0 AND ISNULL(vwR.Premium, 0) > 0 THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
    END AS Quoted,
    CASE
        WHEN ISNULL(grouptt.controlno, 0) <> 0 AND ISNULL(vwR.Premium, 0) > 0  THEN  
            CASE
                    WHEN a.DisplayStatus = 'LOST' THEN 'Quoted_Lost'
                    WHEN a.DisplayStatus = 'DECLINED' THEN 'Quoted_NotTakenUp'
                    WHEN a.DisplayStatus = 'NOT TAKEN UP' THEN 'Quoted_Lost'
                    ELSE a.DisplayStatus
            END
        ELSE a.DisplayStatus
    END AS DisplayStatus
FROM MyTable 

Nested CASE Statement With ELSe (SQL Server)

First, you don't need to nest case statements. Just use one case:

select (CASE WHEN A IS NULL AND B IN ('C', 'D') THEN NULL
             WHEN A IS NULL AND X NOT IN ('C','D') THEN Z
             WHEN A IS NOT NULL THEN SOMETHING_ELSE
        END) as Result

Note that when A IS NULL but the first two conditions are not met, then the return value will be NULL.

Because case statements are evaluated sequentially, this is simpler to write as:

select (CASE WHEN A IS NOT NULL THEN SOMETHING_ELSE
             WHEN B IN ('C', 'D') THEN NULL
             WHEN X NOT IN ('C', 'D') THEN Z
        END) as Result

The first condition captures when A is not NULL. Hence the second two are when A is NULL.

Nested CASE WHEN statement

Something like this? The OUTER APPLY will perform a row-based select which return NULLs in case of your condition not being fullfilled. Otherwise it will return the same values you've specified. This will only work, if you really need the same logic for all columns.

SELECT otherColumn
      ,ConditionalColumns.*
FROM YourTable
OUTER APPLY
(
    SELECT isnull(ddaWC.archexMod,1) as ExperienceMod, 
           case when convert(int,ddawc.premModTtl) <= 0 then 1 
                when convert(int,ddawc.premModTtl) >= 0 
                then (1 + ddaWC.premschmod / ddawc.premModTtl) end as ScheduleMod,
           isnull(ddaWC.TMpercent,1)  as  TerritoryMod, 
           case when convert(int,ddaWC.SchedPercent) = 0 or ddaWC.SchedPercent is not null 
                then (isnull(ddaWC.archexMod,1) 
                         * (convert(decimal(5,2),isnull(ddaWC.SchedPercent,1)))) 
                         * isnull(ddaWC.TMpercent,1)
                when ddaWC.SchedPercent is null 
                then 1 END as EffectiveMod
    WHERE dbo.tblCompanyLocations.CompanyGUID = '29634AF7-D0A2-473D-9574-405C23E10F02' 
          AND dbo.tblQuotes.LineGUID = '1CB72920-B3FC-4822-8030-37B50A2810EB' 
) AS ConditionalColumns

How to apply nested case when statements in the most efficient way possible in SQL?

I would write the following

    case when (case when a.column = 1 then (b.column - c.column) else null end) is not null then 1 
           else null end as txn_rate_flag

as case when a.column = 1 and (b.column - c.column) is not null then 1 else null as txn_rate_flag

Within Where Clause, Nested Case When Statement to return Result with 'OR'

'VIC OR WA' is literally "VIC OR WA", that is why there are no rows returning.
dbo.JunctionT.ProcessState would have to equal "VIC OR WA" (this exact/literal string) to return rows.

What you want instead dbo.JunctionT.ProcessState IN ('VIC','WA')

So there is an element of dynamic SQL involved in order to have your CASE statement return exactly what you need.

---

Related Topics