Mega Code Archive
Case with range
<![CDATA[
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>
]]>