A SELECT statement that uses a searchable CASE function : Case « Query « SQL Server / T-SQL Tutorial





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>










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