With as : With « Transact SQL « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Transact SQL
3. With

20> CREATE TABLE Employees (
21>     EmployeeID int IDENTITY (1, 1) NOT NULL ,
22>     LastName nvarchar (20) NOT NULL ,
23>     FirstName nvarchar (10) NOT NULL ,
24>     Title nvarchar (30) NULL ,
25>     TitleOfCourtesy nvarchar (25) NULL ,
26>     BirthDate datetime NULL ,
27>     HireDate datetime NULL ,
28>     Address nvarchar (60) NULL ,
29>     City nvarchar (15) NULL ,
30>     Region nvarchar (15) NULL ,
31>     PostalCode nvarchar (10) NULL ,
32>     Country nvarchar (15) NULL ,
33>     HomePhone nvarchar (24) NULL ,
34>     Extension nvarchar (4) NULL ,
35>     Photo image NULL ,
36>     Notes ntext NULL ,
37>     ReportsTo int NULL ,
38>     PhotoPath nvarchar (255) NULL
39>
40> )
41> GO

1>
2>
3>
4> WITH Args1 AS
5> (
6>   SELECT LastName, FirstName, BirthDate,
7>     DATEDIFF(year, BirthDate, GETDATE()) AS Diff,
8>     CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
9>   FROM dbo.Employees
10> ),
11>
12> Args2 AS
13> (
14>   SELECT LastName, FirstName, BirthDate, Today,
15>     DATEADD(year, Diff, BirthDate) AS BDCur,
16>     DATEADD(year, Diff + 1, BirthDate) AS BDNxt
17>   FROM Args1
18> ),
19> Args3 AS
20> (
21>   SELECT LastName, FirstName, BirthDate, Today,
22>     BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
23>       THEN 1 ELSE 0 END AS BDCur,
24>     BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
25>       THEN 1 ELSE 0 END AS BDNxt
26>   FROM Args2
27> )
28>
29> SELECT LastName, FirstName, BirthDate,
30>   CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay
31> FROM Args3;
32>
33> drop table Employees;
34> GO
LastName             FirstName  BirthDate               BirthDay
-------------------- ---------- ----------------------- -----------------------
Today                Mary       2008-08-17 00:00:00.000 2008-08-17 00:00:00.000
1>