/*
 --Desc: 在计算员工当前日期的生日时，需要考虑闰年的问题，
   一般在2月29号出生的人在非闰年时生日在3月1号过。
 --Environment:  SQL SERVER 2005
 --Author: Learnsql
*/

USE  Tempdb;
GO
--创建测试表
CREATE TABLE #Employee
(
 [Name] NVARCHAR(10),
 BirthDay DATETIME
);
GO 

--插入数据
INSERT INTO #Employee([Name],BirthDay) VALUES('Tom','1970-1-3');
INSERT INTO #Employee([Name],BirthDay) VALUES('Jim','1971-11-23');
INSERT INTO #Employee([Name],BirthDay) VALUES('Lucy','1972-2-29');
INSERT INTO #Employee([Name],BirthDay) VALUES('Bob','1979-3-15');
INSERT INTO #Employee([Name],BirthDay) VALUES('Jack','1985-5-10');
GO

--生日计算
WITH Emp1([Name],BirthDay,DiffYear,Today) AS --计算当前日期和生日相差的年数
(
  SELECT [Name], BirthDay,
    DATEDIFF(YEAR, BirthDay, GETDATE()) AS DiffYear,
    CAST(CONVERT(NVARCHAR(8), GETDATE(), 112) AS DATETIME) AS Today
  FROM #Employee
),
Emp2([Name],BirthDay,Today,BDCur,BDNxt) AS  --计算当前生日及下一年生日
(
  SELECT [Name],BirthDay, Today,
    DATEADD(YEAR, DiffYear, BirthDay) AS BDCur,
    DATEADD(YEAR, DiffYear + 1, BirthDay) AS BDNxt
  FROM Emp1
),
Emp3([Name],BirthDay,Today,BDCur,BDNxt) AS  --对闰年生日处理
(
  SELECT [Name],BirthDay, Today,
    BDCur + CASE WHEN DAY(BirthDay) = 29 AND DAY(BDCur) = 28
    THEN 1 ELSE 0 END AS BDCur,
    BDNxt + CASE WHEN DAY(BirthDay) = 29 AND DAY(BDNxt) = 28
    THEN 1 ELSE 0 END AS BDNxt
  FROM Emp2
)
SELECT [Name], BirthDay,
  CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDate,
  DATEDIFF(DAY,Today,CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END) AS DiffDay
FROM Emp3;