# Doing a calculation in a Searched CASE : 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.19.Doing a calculation in a Searched CASE
 ```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```
 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