A SELECT statement that uses a searchable CASE function : 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.11.A SELECT statement that uses a searchable CASE function
4>
5create 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>
2INSERT INTO Billings VALUES (11'2005-01-22', 1651,'2005-04-22',123,321);
3> GO

(rows affected)
1INSERT INTO Billings VALUES (22'2001-02-21', 1651,'2002-02-22',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (33'2003-05-02', 1651,'2005-04-12',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (44'1999-03-12', 1651,'2005-04-18',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (55'2000-04-23', 1651,'2005-04-17',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (66'2001-06-14', 1651,'2005-04-18',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (77'2002-07-15', 1651,'2005-04-19',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (88'2003-08-16', 1651,'2005-04-20',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (99'2004-09-17', 1651,'2005-04-21',123,321);
2> GO

(rows affected)
1INSERT INTO Billings VALUES (00'2005-10-18', 1651,'2005-04-22',123,321);
2> GO

(rows affected)
1>
2>
3SELECT 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
11FROM Billings
12WHERE BillingTotal - PaymentTotal - CreditTotal > 0
13> GO
BillingNumber BillingTotal BillingDate             BillingDueDate          Status
------------- ------------ ----------------------- ----------------------- ---------------------

(rows affected)
1>
2>
3> drop table Billings;
4> GO
1>
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.