SQL Server/T-SQL Tutorial/Query/Case — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:23, 26 мая 2010
Содержание
- 1 Alias for case statement
- 2 A SELECT statement that uses a searchable CASE function
- 3 A SELECT statement that uses a simple CASE function
- 4 A Simple CASE
- 5 Automatic Code Generation for CASE Expressions
- 6 CASE Expression
- 7 Case then with query
- 8 Case when else
- 9 Case with range
- 10 Doing a calculation in a Searched CASE
- 11 Searched CASE expression looks for the first expression that evaluates to true.
- 12 Summarizing Data Using the CASE Expression
- 13 Summarizing Data Using the Searched CASE Expression
- 14 The syntax of the searched CASE function
- 15 The syntax pattern for case
- 16 Update statement based on case condition
- 17 Use two case statements in one select statement
- 18 Using a CASE expression to sum sales by weekday.
- 19 Using the CASE Expression with Complex Conditions
Alias for case statement
<source lang="sql">
4> CREATE TABLE Product( 5> ProductID int NOT NULL, 6> Name nvarchar(25) NOT NULL, 7> ProductNumber nvarchar(25) , 8> Color nvarchar(15) NULL, 9> StandardCost money NOT NULL, 10> Size nvarchar(5) NULL, 11> Weight decimal(8, 2) NULL, 12> ProductLine nchar(20) NULL, 13> SellStartDate datetime NOT NULL, 14> SellEndDate datetime NULL 15> ) 16> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 2> GO (1 rows affected) 1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 2> GO (1 rows affected) 1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 2> GO (1 rows affected) 1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 2> GO (1 rows affected) 1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 2> GO (1 rows affected) 1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 2> GO (1 rows affected) 1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 2> GO (1 rows affected) 1> 2> 3> 4> SELECT ProductID 5> , Name 6> , CASE ProductID 7> WHEN 1 THEN "Mountain" 8> WHEN 2 THEN "Road" 9> WHEN 3 THEN "Touring" 10> WHEN Null THEN "Something" 11> ELSE "No" 12> END As SubCategory 13> FROM Product 14> GO ProductID Name SubCategory
------------------------- -----------
1 Product A Mountain
2 Product B Road
3 Product C Touring
4 Product D No
5 Product E No
6 Product F No
7 Product G No
8 Product H No
9 Product I No
0 Product J No
(10 rows affected) 1> 2> 3> drop table Product; 4> GO</source>
A SELECT statement that uses a searchable CASE function
<source lang="sql">
4> 5> create table Billings ( 6> BankerID INTEGER, 7> BillingNumber INTEGER, 8> BillingDate datetime, 9> BillingTotal INTEGER, 10> TermsID INTEGER, 11> BillingDueDate datetime , 12> PaymentTotal INTEGER, 13> CreditTotal INTEGER 14> 15> ); 16> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> SELECT BillingNumber, BillingTotal, BillingDate, BillingDueDate, 4> CASE 5> WHEN DATEDIFF(day, BillingDueDate, GETDATE()) > 30 6> THEN "Over 30 days past due" 7> WHEN DATEDIFF(day, BillingDueDate, GETDATE()) > 0 8> THEN "1 to 30 days past due" 9> ELSE "Current" 10> END AS Status 11> FROM Billings 12> WHERE BillingTotal - PaymentTotal - CreditTotal > 0 13> GO BillingNumber BillingTotal BillingDate BillingDueDate Status
------------ ----------------------- ----------------------- ---------------------
(0 rows affected) 1> 2> 3> drop table Billings; 4> GO 1></source>
A SELECT statement that uses a simple CASE function
<source lang="sql">
5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> SELECT BillingNumber, TermsID, 4> CASE TermsID 5> WHEN 1 THEN "Net due 10 days" 6> WHEN 2 THEN "Net due 20 days" 7> WHEN 3 THEN "Net due 30 days" 8> WHEN 4 THEN "Net due 60 days" 9> WHEN 5 THEN "Net due 90 days" 10> END AS Terms 11> FROM Billings 12> GO BillingNumber TermsID Terms
----------- ---------------
1 1 Net due 10 days
2 1 Net due 10 days
3 1 Net due 10 days
4 1 Net due 10 days
5 1 Net due 10 days
6 1 Net due 10 days
7 1 Net due 10 days
8 1 Net due 10 days
9 1 Net due 10 days
0 1 Net due 10 days
(10 rows affected) 1> 2> 3> drop table Billings; 4> GO</source>
A Simple CASE
<source lang="sql">
5> 6> 7> CREATE TABLE Orders ( 8> OrderID int NOT NULL , 9> CustomerID nchar (5) NULL , 10> EmployeeID int NULL , 11> OrderDate datetime NULL , 12> RequiredDate datetime NULL , 13> ShippedDate datetime NULL , 14> ShipVia int NULL , 15> Freight money NULL DEFAULT (0), 16> ShipName nvarchar (40) NULL , 17> ShipAddress nvarchar (60) NULL , 18> ShipCity nvarchar (15) NULL , 19> ShipRegion nvarchar (15) NULL , 20> ShipPostalCode nvarchar (10) NULL , 21> ShipCountry nvarchar (15) NULL) 22> GO 1> 2> 3> SELECT TOP 10 OrderID, OrderID % 10 AS "Last Digit", Position = 4> CASE OrderID % 10 5> WHEN 1 THEN "First" 6> WHEN 2 THEN "Second" 7> WHEN 3 THEN "Third" 8> WHEN 4 THEN "Fourth" 9> ELSE "Something Else" 10> END 11> FROM Orders 12> GO OrderID Last Digit Position
----------- --------------
(0 rows affected) 1> 2> drop table orders; 3> GO</source>
Automatic Code Generation for CASE Expressions
<source lang="sql">
6> 7> 8> CREATE TABLE titles( 9> title_id varchar(20), 10> title varchar(80) NOT NULL, 11> type char(12) NOT NULL, 12> pub_id char(4) NULL, 13> price money NULL, 14> advance money NULL, 15> royalty int NULL, 16> ytd_sales int NULL, 17> notes varchar(200) NULL, 18> pubdate datetime NOT NULL 19> ) 20> 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> select distinct "WHEN """ 2> + SUBSTRING(title_id,1,2) 3> + """ THEN" from titles 4> GO
WHEN "1" THEN WHEN "2" THEN WHEN "3" THEN WHEN "4" THEN WHEN "5" THEN WHEN "6" THEN WHEN "7" THEN WHEN "8" THEN (8 rows affected) 1> 2> drop table titles; 3> GO 1> 2></source>
CASE Expression
<source lang="sql">
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> 3> SELECT title_id, 4> CASE SUBSTRING(title_id,1,2) 5> WHEN "BU" THEN "Business" 6> WHEN "MC" THEN "Modern Cooking" 7> WHEN "PC" THEN "Popular Computing" 8> WHEN "PS" THEN "Psychology" 9> WHEN "TC" THEN "Traditional Cooking" 10> END AS BookType 11> FROM titles 12> GO title_id BookType
-------------------
1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 6 NULL 7 NULL 8 NULL (8 rows affected) 1> 2> drop table titles; 3> GO 1></source>
Case then with query
<source lang="sql">
3> CREATE TABLE titles( 4> title_id varchar(20), 5> title varchar(80) NOT NULL, 6> type char(12) NOT NULL, 7> pub_id char(4) NULL, 8> price money NULL, 9> advance money NULL, 10> royalty int NULL, 11> ytd_sales int NULL, 12> notes varchar(200) NULL, 13> pubdate datetime NOT NULL 14> ) 15> 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> 3> SELECT title, pub_id, 4> CASE WHEN price IS NULL THEN (SELECT MIN(price) FROM titles) 5> ELSE price 6> END 7> FROM titles 8> 9> GO title pub_id
------ ---------------------
Secrets 1389 20.0000 The 1389 19.9900 Emotional 0736 7.9900 Prolonged 0736 19.9900 With 1389 11.9500 Valley 0877 19.9900 Any? 0877 14.9900 Fifty 0877 11.9500 (8 rows affected) 1> 2> 3> drop table titles; 4> GO 1> 2></source>
Case when else
<source lang="sql">
7> CREATE TABLE Product( 8> ProductID int NOT NULL, 9> Name nvarchar(25) NOT NULL, 10> ProductNumber nvarchar(25) , 11> Color nvarchar(15) NULL, 12> StandardCost money NOT NULL, 13> Size nvarchar(5) NULL, 14> Weight decimal(8, 2) NULL, 15> ProductLine nchar(20) NULL, 16> SellStartDate datetime NOT NULL, 17> SellEndDate datetime NULL 18> ) 19> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> 6> SELECT ProductID 7> , Name 8> , SubCategory = CASE ProductID 9> WHEN 1 THEN "Mountain Bike" 10> WHEN 2 THEN "Road Bike" 11> WHEN 3 THEN "Touring Bike" 12> WHEN Null THEN "Something Else" 13> ELSE "(No Subcategory)" 14> END 15> FROM Product 16> GO ProductID Name SubCategory
------------------------- ----------------
1 Product A Mountain Bike
2 Product B Road Bike
3 Product C Touring Bike
4 Product D (No Subcategory)
5 Product E (No Subcategory)
6 Product F (No Subcategory)
7 Product G (No Subcategory)
8 Product H (No Subcategory)
9 Product I (No Subcategory)
0 Product J (No Subcategory)
(10 rows affected) 1> 2> 3> drop table Product; 4> GO</source>
Case with range
<source lang="sql">
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></source>
Doing a calculation in a Searched CASE
<source lang="sql">
5> 6> 7> CREATE TABLE OrderDetails ( 8> OrderID int NOT NULL , 9> ProductID int NOT NULL , 10> UnitPrice money NOT NULL DEFAULT (0), 11> Quantity smallint NOT NULL DEFAULT (1), 12> Discount real NOT NULL DEFAULT (0) 13> ) 14> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 11> 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 rows affected) (1 rows affected) 1> 2> 3> SELECT TOP 10 OrderID % 10 AS "Last Digit", 4> ProductID, 5> "How Close?" = CASE 6> WHEN (OrderID % 10) < 3 THEN "Ends With Less Than Three" 7> WHEN ProductID = 6 THEN "ProductID is 6" 8> WHEN ABS(OrderID % 10 - ProductID) <= 1 THEN "Within 1" 9> ELSE "More Than One Apart" 10> END 11> FROM OrderDetails 12> WHERE ProductID < 10 13> ORDER BY OrderID DESC 14> GO Last Digit ProductID How Close?
----------- -------------------------
(0 rows affected) 1> 2> drop table OrderDetails; 3> GO</source>
Searched CASE expression looks for the first expression that evaluates to true.
<source lang="sql">
If none of the WHEN conditions evaluates to true, the value of the ELSE expression is returned. 9> CREATE TABLE employee( 10> id INTEGER NOT NULL PRIMARY KEY, 11> first_name VARCHAR(10), 12> last_name VARCHAR(10), 13> salary DECIMAL(10,2), 14> start_Date DATETIME, 15> region VARCHAR(10), 16> city VARCHAR(20), 17> managerid INTEGER 18> ); 19> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> 4> SELECT first_name, 5> CASE 6> WHEN salary > 0 AND salary < 100000 THEN 1 7> WHEN salary >= 100000 AND salary < 200000 THEN 2 8> WHEN salary >= 200000 AND salary < 300000 THEN 3 9> ELSE 4 10> END budget_weight 11> FROM Employee 12> GO first_name budget_weight
-------------
Jason 1 Alison 1 James 1 Celia 1 Robert 1 Linda 1 David 1 James 1 Joan 1 (9 rows affected) 1> 2> 3> drop table employee; 4> GO</source>
Summarizing Data Using the CASE Expression
<source lang="sql">
Selecting the total sales by weekday--simple query. 8> 9> CREATE TABLE sales( 10> stor_id char(4) NOT NULL, 11> ord_num varchar(20) NOT NULL, 12> ord_date datetime NOT NULL, 13> qty smallint NOT NULL, 14> payterms varchar(12) NOT NULL, 15> title_id varchar(80) 16> ) 17> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT sum(qty) AS Total, 4> datename(weekday, ord_date) AS Weekday 5> FROM sales 6> GROUP BY datename(weekday, ord_date) 7> GO Total Weekday
------------------------------
75 Tuesday
80 Wednesday
(2 rows affected) 1> 2> drop table sales; 3> GO 1></source>
Summarizing Data Using the Searched CASE Expression
<source lang="sql">
6> 7> CREATE TABLE stores( 8> stor_id char(4) NOT NULL, 9> stor_name varchar(40) NULL, 10> stor_address varchar(40) NULL, 11> city varchar(20) NULL, 12> state char(2) NULL, 13> zip char(5) NULL 14> ) 15> GO 1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789") 2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745") 3> insert stores values("3","T","679 St.", "Portland", "OR","89076") 4> insert stores values("4","F","89 St.", "Fremont", "CA","90019") 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE sales( 3> stor_id char(4) NOT NULL, 4> ord_num varchar(20) NOT NULL, 5> ord_date datetime NOT NULL, 6> qty smallint NOT NULL, 7> payterms varchar(12) NOT NULL, 8> title_id varchar(80) 9> ) 10> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT DISTINCT st.stor_name, 3> "Sales Rating" = 4> CASE 5> WHEN (SELECT SUM(sa.qty) FROM sales sa 6> WHERE st.stor_id = sa.stor_id) < 10 7> THEN "Poor" 8> WHEN (SELECT SUM(sa.qty) FROM sales sa 9> WHERE st.stor_id = sa.stor_id) < 80 10> THEN "Average" 11> WHEN (SELECT SUM(sa.qty) FROM sales sa 12> WHERE st.stor_id = sa.stor_id) < 100 13> THEN "Good" 14> ELSE "Excellent" 15> END, 16> "Sales Total" = (SELECT SUM(sa.qty) FROM sales sa 17> WHERE st.stor_id = sa.stor_id) 18> FROM stores st, sales sa 19> ORDER BY "Sales Total" 20> GO stor_name Sales Rating Sales Total
------------ -----------
N Average 10 T Average 20 F Average 25 B Average 75 (4 rows affected) 1> drop table stores; 2> drop table sales; 3> GO 1> 2></source>
The syntax of the searched CASE function
<source lang="sql">
CASE
WHEN conditional_expression_1 THEN result_expression_1 [WHEN conditional_expression_2 THEN result_expression_2]... [ELSE else_result_expression]
END</source>
The syntax pattern for case
<source lang="sql">
SELECT CASE value to evaluate WHEN literal value 1 THEN return value WHEN literal value 2 THEN return value ... END 10> 11> 12> DECLARE @Status Int 13> SET @Status = 1 14> SELECT CASE @Status 15> WHEN 1 THEN "Active" 16> WHEN 2 THEN "Inactive" 17> WHEN 3 THEN "Pending" 18> END 19> GO
Active (1 rows affected)</source>
Update statement based on case condition
<source lang="sql">
5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> UPDATE Employee 3> SET salary = CASE 4> WHEN salary > 0 and salary < 100000 THEN salary* 1.2 5> WHEN salary > = 100000 and salary < 200000 THEN salary* 1.1 6> ELSE salary* 1.05 7> END 8> GO (9 rows affected) 1> 2> 3> drop table employee; 4> GO</source>
Use two case statements in one select statement
<source lang="sql">
21> create table Bankers( 22> BankerID Integer, 23> BankerName VARCHAR(20), 24> BankerContactLName VARCHAR(20), 25> BankerContactFName VARCHAR(20), 26> BankerCity VARCHAR(20), 27> BankerState VARCHAR(20), 28> BankerZipCode VARCHAR(20), 29> BankerPhone VARCHAR(20) 30> ) 31> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> 5> SELECT 6> CASE 7> WHEN GROUPING(BankerState) = 1 THEN "All" 8> ELSE BankerState 9> END AS BankerState, 10> CASE 11> WHEN GROUPING(BankerCity) = 1 THEN "All" 12> ELSE BankerCity 13> END AS BankerCity, 14> COUNT(*) AS QtyBankers 15> FROM Bankers 16> WHERE BankerState IN ("IA", "NJ") 17> GROUP BY BankerState, BankerCity WITH ROLLUP 18> ORDER BY BankerState DESC, BankerCity DESC 19> GO BankerState BankerCity QtyBankers
-------------------- -----------
(0 rows affected) 1> 2> drop table Bankers; 3> GO 1> 2></source>
Using a CASE expression to sum sales by weekday.
<source lang="sql">
4> 5> 6> CREATE TABLE sales( 7> stor_id char(4) NOT NULL, 8> ord_num varchar(20) NOT NULL, 9> ord_date datetime NOT NULL, 10> qty smallint NOT NULL, 11> payterms varchar(12) NOT NULL, 12> title_id varchar(80) 13> ) 14> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT sum(qty) AS Total, 4> CASE datepart(weekday, ord_date) 5> WHEN 1 THEN "Sunday" 6> WHEN 2 THEN "Monday" 7> WHEN 3 THEN "Tuesday" 8> WHEN 4 THEN "Wednesday" 9> WHEN 5 THEN "Thursday" 10> WHEN 6 THEN "Friday" 11> WHEN 7 THEN "Saturday" 12> END AS Weekday 13> FROM sales 14> GROUP BY datepart(weekday, ord_date) 15> GO Total Weekday
---------
75 Tuesday
80 Wednesday
(2 rows affected) 1> drop table sales; 2> GO</source>
Using the CASE Expression with Complex Conditions
<source lang="sql">
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></source>
