Mega Code Archive
Using the CASE Expression with Complex Conditions
<![CDATA[
5>
6> CREATE TABLE titles(
7> title_id varchar(20),
8> title varchar(80) NOT NULL,
9> type char(12) NOT NULL,
10> pub_id char(4) NULL,
11> price money NULL,
12> advance money NULL,
13> royalty int NULL,
14> ytd_sales int NULL,
15> notes varchar(200) NULL,
16> pubdate datetime NOT NULL
17> )
18> 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_id, price, ytd_sales,
4> "DiscountPrice" =
5> CASE
6> WHEN ytd_sales < 1000 THEN
7> CONVERT (SMALLMONEY, price * .50)
8> WHEN ytd_sales < 3000 THEN
9> CONVERT (SMALLMONEY, price * .85)
10> ELSE price
11> END
12> FROM titles
13> GO
title_id price ytd_sales DiscountPrice
-------------------- --------------------- ----------- ---------------------
1 20.0000 4095 20.0000
2 19.9900 4095 19.9900
3 7.9900 3336 7.9900
4 19.9900 4072 19.9900
5 11.9500 3876 11.9500
6 19.9900 2032 16.9915
7 14.9900 4095 14.9900
8 11.9500 1509 10.1575
(8 rows affected)
1>
2> drop table titles;
3> GO
1>
2>
]]>