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

Home
SQL Server / T-SQL Tutorial
1.Query
2.Insert Delete Update
3.Table
4.Table Join
5.Data Types
6.Set Operations
7.Constraints
8.Subquery
9.Aggregate Functions
10.Date Functions
11.Math Functions
12.String Functions
13.Data Convert Functions
14.Analytical Functions
15.Sequence Indentity
16.View
17.Index
18.Cursor
19.Database
20.Transact SQL
21.Procedure Function
22.Trigger
23.Transaction
24.XML
25.System Functions
26.System Settings
27.System Tables Views
28.User Role
29.CLR
SQL Server / T-SQL Tutorial » Query » Case 
1.23.18.Case with range
5>
6>
7CREATE 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>
2insert titles values ('1''Secrets',   'popular_comp', '1389', $20.00, $8000.00, 104095,'Note 1','06/12/94')
3insert titles values ('2''The',       'business',     '1389', $19.99, $5000.00, 104095,'Note 2','06/12/91')
4insert titles values ('3''Emotional', 'psychology',   '0736', $7.99,  $4000.00, 103336,'Note 3','06/12/91')
5insert titles values ('4''Prolonged', 'psychology',   '0736', $19.99, $2000.00, 104072,'Note 4','06/12/91')
6insert titles values ('5''With',      'business',     '1389', $11.95, $5000.00, 103876,'Note 5','06/09/91')
7insert titles values ('6''Valley',    'mod_cook',     '0877', $19.99, $0.00,    122032,'Note 6','06/09/91')
8insert titles values ('7''Any?',      'trad_cook',    '0877', $14.99, $8000.00, 104095,'Note 7','06/12/91')
9insert titles values ('8''Fifty',     'trad_cook',    '0877', $11.95, $4000.00, 141509,'Note 8','06/12/91')
10> GO

(rows affected)

(rows affected)

(rows affected)

(rows affected)

(rows affected)

(rows affected)

(rows affected)

(rows affected)
1>
2SELECT
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
11FROM 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

(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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.