Lesson 3: CASE Expressions and Robust Business Logic

Section 4 — Lesson 3: CASE Expressions and Robust Business Logic

Welcome back. You now have two advanced tools that separate beginners from job-ready DBAs:
CTEs and window functions.

In the real world, you will constantly be asked to translate messy business rules into clean, safe SQL:
“Flag risky records,” “Map internal codes to readable labels,” “Create tiers,” “Handle NULLs,”
“Detect anomalies,” and “Produce a report that leadership can understand in one glance.”

That is exactly what CASE expressions are for.

Lesson description: You will learn how CASE works (simple vs searched CASE),
how DBAs use CASE to make reports readable, how to build safe classifications and flags,
how to handle NULL values correctly, how to avoid common mistakes (overlapping conditions, implicit conversions),
and how to combine CASE with window functions for professional-quality reporting and anomaly detection.

1) What CASE Is (And Why DBAs Use It)

A CASE expression is SQL’s built-in way to write conditional logic:
“If this is true, return that; otherwise return something else.”

DBAs use CASE to:

• convert codes into readable labels (e.g., status codes → “Active/Inactive”)
• create tiers (Gold/Silver/Bronze customers)
• flag data quality issues (missing email, invalid date ranges)
• build KPI logic (SLA met/not met, risk levels)
• normalize outputs for consistent reporting (“Unknown” instead of NULL)

DBA rule: CASE should make your output clearer and safer, not more confusing.
If a CASE becomes too complex, break logic into CTE stages.

2) The Two Types of CASE

2A) Simple CASE (matches one expression to values)

Use this when you are matching a single column to specific values.

SELECT
    Status,
    CASE Status
        WHEN 'NEW'       THEN 'New Booking'
        WHEN 'CONFIRMED' THEN 'Confirmed Booking'
        WHEN 'CANCELLED' THEN 'Cancelled Booking'
        ELSE 'Other / Unknown'
    END AS StatusLabel
FROM dbo.Booking;

2B) Searched CASE (checks conditions)

This is the most common for DBA work. You can test ranges, NULLs, patterns, and multiple columns.

SELECT
    TotalDue,
    CASE
        WHEN TotalDue >= 5000 THEN 'High Value'
        WHEN TotalDue >= 1000 THEN 'Medium Value'
        WHEN TotalDue >  0    THEN 'Low Value'
        ELSE 'Zero / Unknown'
    END AS ValueTier
FROM Sales.SalesOrderHeader;

DBA best practice: Put conditions in correct order (highest threshold first) to avoid incorrect classification.

3) CASE + NULL Handling (Professional Output)

NULLs are normal in real data. DBAs do not let NULLs silently ruin reporting.
CASE is often used to make NULL behavior explicit.

3A) Turn NULL into readable values

SELECT
    c.CustomerID,
    CASE
        WHEN p.BusinessEntityID IS NULL THEN 'No Person Record (Store/Other)'
        ELSE p.FirstName + ' ' + p.LastName
    END AS CustomerDisplayName
FROM Sales.Customer AS c
LEFT JOIN Person.Person AS p
    ON c.PersonID = p.BusinessEntityID;

3B) Detect missing “required” values (data-quality flags)

DBAs often build reports that highlight missing key fields.

SELECT
    c.CustomerID,
    ea.EmailAddress,
    CASE
        WHEN ea.EmailAddress IS NULL OR LTRIM(RTRIM(ea.EmailAddress)) = '' THEN 1
        ELSE 0
    END AS IsEmailMissing
FROM Person.EmailAddress AS ea
RIGHT JOIN Person.Person AS p
    ON ea.BusinessEntityID = p.BusinessEntityID
RIGHT JOIN Sales.Customer AS c
    ON c.PersonID = p.BusinessEntityID;

DBA note: In a real application database, “missing required data” can be a root cause of failed processes.

4) CASE as “Business Rules” (Tiering, SLA, Risk Flags)

CASE is commonly used to encode business decisions in reports.
Your output should be easy for a non-technical person to interpret.

4A) Customer tiering by lifetime spend (AdventureWorks)

This is a realistic business request: classify customers by value.

;WITH CustomerSpend AS
(
    SELECT
        soh.CustomerID,
        SUM(soh.TotalDue) AS TotalSpent
    FROM Sales.SalesOrderHeader AS soh
    GROUP BY soh.CustomerID
)
SELECT
    cs.CustomerID,
    cs.TotalSpent,
    CASE
        WHEN cs.TotalSpent >= 100000 THEN 'Platinum'
        WHEN cs.TotalSpent >= 50000  THEN 'Gold'
        WHEN cs.TotalSpent >= 10000  THEN 'Silver'
        WHEN cs.TotalSpent >  0      THEN 'Bronze'
        ELSE 'No Spend'
    END AS CustomerTier
FROM CustomerSpend AS cs
ORDER BY cs.TotalSpent DESC;

DBA best practice: Explicit thresholds, explicit “No Spend,” and a clear order for evaluation.

4B) “Risk flags” based on anomalies

DBAs often build a “risk flag” column that highlights rows requiring attention.

SELECT TOP (200)
    soh.SalesOrderID,
    soh.OrderDate,
    soh.SubTotal,
    soh.Freight,
    soh.TotalDue,
    CASE
        WHEN soh.SubTotal <= 0 THEN 'Data Issue: SubTotal <= 0' WHEN soh.Freight / NULLIF(soh.SubTotal, 0) >= 0.25 THEN 'Risk: High Freight %'
        WHEN soh.TotalDue >= 10000 THEN 'Review: Very High Order'
        ELSE 'Normal'
    END AS ReviewFlag
FROM Sales.SalesOrderHeader AS soh
ORDER BY soh.OrderDate DESC;

DBA note: Flags like this are used for auditing, finance checks, and operational review queues.

5) CASE + Aggregates (Conditional Counting and Sums)

DBAs often need “counts by category” or “how many are missing required fields.”
CASE can be used inside SUM/COUNT to do conditional aggregates.

5A) Count orders by value tier (conditional aggregation)

SELECT
    SUM(CASE WHEN soh.TotalDue >= 5000 THEN 1 ELSE 0 END) AS HighValueOrders,
    SUM(CASE WHEN soh.TotalDue >= 1000 AND soh.TotalDue < 5000 THEN 1 ELSE 0 END) AS MediumValueOrders, SUM(CASE WHEN soh.TotalDue > 0 AND soh.TotalDue < 1000 THEN 1 ELSE 0 END) AS LowValueOrders,
    COUNT(*) AS TotalOrders
FROM Sales.SalesOrderHeader AS soh;

DBA tip: Conditional aggregation is one of the cleanest ways to produce KPI summaries.

6) CASE + Window Functions (Professional Reporting)

Combining CASE with window functions creates advanced reports without complicated stored procedures.
Example: classify customers, then rank them within tiers.

;WITH CustomerSpend AS
(
    SELECT
        soh.CustomerID,
        SUM(soh.TotalDue) AS TotalSpent
    FROM Sales.SalesOrderHeader AS soh
    GROUP BY soh.CustomerID
),
Tiered AS
(
    SELECT
        cs.CustomerID,
        cs.TotalSpent,
        CASE
            WHEN cs.TotalSpent >= 100000 THEN 'Platinum'
            WHEN cs.TotalSpent >= 50000  THEN 'Gold'
            WHEN cs.TotalSpent >= 10000  THEN 'Silver'
            WHEN cs.TotalSpent >  0      THEN 'Bronze'
            ELSE 'No Spend'
        END AS Tier
    FROM CustomerSpend AS cs
),
Ranked AS
(
    SELECT
        t.CustomerID,
        t.TotalSpent,
        t.Tier,
        ROW_NUMBER() OVER (PARTITION BY t.Tier ORDER BY t.TotalSpent DESC, t.CustomerID ASC) AS TierRank
    FROM Tiered AS t
)
SELECT
    r.Tier,
    r.TierRank,
    r.CustomerID,
    r.TotalSpent
FROM Ranked AS r
WHERE r.Tier IN ('Platinum','Gold','Silver')
  AND r.TierRank <= 20
ORDER BY r.Tier, r.TierRank;

DBA outcome: You now have “Top 20 customers per tier” with clean, auditable logic.

7) Common CASE Mistakes (And DBA Fixes)

Mistake A: Overlapping conditions

If your conditions overlap, rows can be classified incorrectly.
Fix by ordering conditions from most specific/highest threshold to lowest.

Mistake B: Missing ELSE (creating silent NULL categories)

If you omit ELSE, SQL returns NULL for unmatched rows.
DBAs usually include ELSE for predictable outputs (even if it’s “Unknown”).

Mistake C: Implicit conversions

CASE returns one data type. If one branch returns text and another returns numeric, SQL Server may convert unexpectedly.
Keep outputs consistent (all numeric, or all text). Cast explicitly when needed.

Mistake D: CASE in WHERE (performance and logic confusion)

You can use CASE in WHERE, but it often harms clarity.
DBAs typically compute the CASE in a CTE, then filter on the computed column.

8) Practice (Job-Ready Exercises)

Do these in AdventureWorks and save the scripts:

1. Build a query that classifies orders into High/Medium/Low tiers based on TotalDue (use searched CASE).
2. Create a “review flag” query that labels suspicious orders (high freight %, subtotal <= 0, extremely high totals).
3. Use conditional aggregation to count how many orders fall into each tier.
4. Combine CASE + ROW_NUMBER to show the Top 10 customers inside each tier.
5. Validate that no row is unclassified (avoid missing ELSE).

Lesson Summary

• CASE translates business rules into SQL outputs that humans can understand.
• Use simple CASE for value mapping, searched CASE for condition-based logic.
• DBAs use CASE for tiers, flags, anomaly detection, and data-quality reporting.
• CASE inside aggregates enables KPI-style conditional counts and sums.
• Combine CASE with CTEs and window functions for clean, job-ready reporting scripts.

Next Lesson: Date/Time Handling Pitfalls (GETDATE vs SYSDATETIME, time zones concept)

Next, you will learn how DBAs handle date/time correctly—one of the most common sources of bugs,
incorrect reports, and production incidents. You will learn precision, time zones, and safe filtering patterns.