Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Case Statement Tricks

By Neil Boyle,

The CASE statement is a very flexible tool. Here are just a few of the tricks you can work with it.

Item translation

One of the simplest things you can do is to derive a columns contents based on it’s contents, or the contents of another data item.

SELECT  pub_name,
        CASE WHEN state is NULL or state = ''
            THEN 'Not supplied'
            ELSE state
        END
FROM publishers

SELECT CASE WHEN datepart (dw, getdate()) In (1, 7)
            THEN 'Weekend overtime rates apply :-)'
            ELSE 'Standard rates apply'
END

Titling subtotals

You can use CUBE and ROLLUP to produce totals and subtotals within a result set, but because SQL Server places NULLS for non-totaled columns in the total rows, they do not always look very pretty. In this example we use the CASE statement to place emphasis on the subtotal lines.

SELECT   CASE GROUPING(stor_name)
                    WHEN 1 then '[State total]'
                    ELSE stor_name
                End as store,
                state,
                sum(qty)
                FROM stores s
JOIN      sales sl on s.stor_id = sl.stor_id
GROUP  BY stor_name, state
WITH     CUBE
HAVING GROUPING(state) = 0
ORDER   BY state, GROUPING(stor_name)

Simplifying Output

This batch lists of sales by store, and gives a general indication of the stores performance against the average quantity of sales per store.

declare @storeAvg int
select @storeAvg = avg(storeTotal)
from (
    select stor_id,
    sum(qty) as storeTotal
    from sales
    group by stor_id
) as derived


select  stor_name,
        storeTotal,
case    when storeTotal > @storeAvg * 1.1
            then 'Above average'
        when storeTotal < @storeAvg * 0.9
            then 'Below average'
        else 'About average'
end as storePerformance
from (
        select  stor_id,
                sum(qty) as storeTotal
                from     sales
                group    by stor_id
) as derivedStores
join stores
on stores.stor_id = derivedStores.stor_id

This query batch starts by calculating the average of total sales per store, which is stored in the variable @storeAvg, then that average is used to rate the stores sales performance.

Grouping data into ranges

This example calculates the number of orders in certain size ranges. It also shows off the power of SQL as a set-based language: to code this as in a procedural language like VB would not be able to do this in a single statement.

SELECT    SUM (CASE when qty <= 5 then 1 else 0 end) 
            as '1_to_5',
          SUM (CASE when qty between 6 and 10 then 1 else 0 end) 
            as '6_to_10',
          SUM (CASE when qty between 11 and 20 then 1 else 0 end) 
            as '11_to_20',
          SUM (CASE when qty > 20 then 1 else 0 end) 
            as '20_plus'
FROM sales

Complex Sequencing

CASE statements can be used in an ‘ORDER BY clause to order non-sequential data. This example sorts stores by name, but puts all the stores in California at the top of the list.

SELECT   stor_name, state
FROM     stores
ORDER    BY CASE state
             WHEN 'CA' then 1
             ELSE 2
         END,
         stor_name

Further reading

This article discusses how to increase performance of complex UPDATE statements by using CASE.

This SQL 2000 article contains a section that shows how to use the CASE statement in a User Defined Function to enforce complex declarative constraints

About the author

Neil Boyle is an independent SQL Server consultant working out of London, England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk

Total article views: 14239 | Views in the last 30 days: 13
 
Related Articles
FORUM

Query Structure Grouping/Averaging

Query Structure Grouping/Averaging

FORUM

Moving Average Over a Data Table Requiring Group

Moving Average Over a Diverse Table

FORUM

grouping based on parameter selected

grouping based on parameter selected

FORUM

where is the database 'state' is stored

where is the database 'state' is stored

FORUM

Workout the average

Help with average calculation

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones