Case with range : Case « Query « SQL Server / T-SQL Tutorial





5>
6>
7> CREATE TABLE titles(
8>    title_id       varchar(20),
9>    title          varchar(80)       NOT NULL,
10>    type           char(12)          NOT NULL,
11>    pub_id         char(4)               NULL,
12>    price          money                 NULL,
13>    advance        money                 NULL,
14>    royalty        int                   NULL,
15>    ytd_sales      int                   NULL,
16>    notes          varchar(200)          NULL,
17>    pubdate        datetime          NOT NULL
18> )
19> GO
1>
2> insert titles values ('1', 'Secrets',   'popular_comp', '1389', $20.00, $8000.00, 10, 4095,'Note 1','06/12/94')
3> insert titles values ('2', 'The',       'business',     '1389', $19.99, $5000.00, 10, 4095,'Note 2','06/12/91')
4> insert titles values ('3', 'Emotional', 'psychology',   '0736', $7.99,  $4000.00, 10, 3336,'Note 3','06/12/91')
5> insert titles values ('4', 'Prolonged', 'psychology',   '0736', $19.99, $2000.00, 10, 4072,'Note 4','06/12/91')
6> insert titles values ('5', 'With',      'business',     '1389', $11.95, $5000.00, 10, 3876,'Note 5','06/09/91')
7> insert titles values ('6', 'Valley',    'mod_cook',     '0877', $19.99, $0.00,    12, 2032,'Note 6','06/09/91')
8> insert titles values ('7', 'Any?',      'trad_cook',    '0877', $14.99, $8000.00, 10, 4095,'Note 7','06/12/91')
9> insert titles values ('8', 'Fifty',     'trad_cook',    '0877', $11.95, $4000.00, 14, 1509,'Note 8','06/12/91')
10> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> SELECT
3> title,
4> price,
5> 'classification'=CASE
6>     WHEN price < 10.00 THEN 'Low Priced'
7>     WHEN price BETWEEN 10.00 AND 20.00 THEN 'Moderately Priced'
8>     WHEN price > 20.00 THEN 'Expensive'
9>     ELSE 'Unknown'
10>     END
11> FROM titles
12> GO
title                                                                            price                 classification
-------------------------------------------------------------------------------- --------------------- -----------------
Secrets                                                                                        20.0000 Moderately Priced
The                                                                                            19.9900 Moderately Priced
Emotional                                                                                       7.9900 Low Priced
Prolonged                                                                                      19.9900 Moderately Priced
With                                                                                           11.9500 Moderately Priced
Valley                                                                                         19.9900 Moderately Priced
Any?                                                                                           14.9900 Moderately Priced
Fifty                                                                                          11.9500 Moderately Priced

(8 rows affected)
1>
2>
3> drop table titles;
4> GO
1>
2>










1.23.Case
1.23.1.The syntax pattern for case
1.23.2.The syntax of the searched CASE function
1.23.3.A Simple CASE
1.23.4.CASE Expression
1.23.5.Automatic Code Generation for CASE Expressions
1.23.6.Summarizing Data Using the CASE Expression
1.23.7.Using a CASE expression to sum sales by weekday.
1.23.8.Using the CASE Expression with Complex Conditions
1.23.9.Summarizing Data Using the Searched CASE Expression
1.23.10.A SELECT statement that uses a simple CASE function
1.23.11.A SELECT statement that uses a searchable CASE function
1.23.12.Searched CASE expression looks for the first expression that evaluates to true.
1.23.13.Update statement based on case condition
1.23.14.Case when else
1.23.15.Alias for case statement
1.23.16.Use two case statements in one select statement
1.23.17.Case then with query
1.23.18.Case with range
1.23.19.Doing a calculation in a Searched CASE