Mega Code Archive
With as
<![CDATA[
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>
]]>