|
|
The mission is to calculate next birthday of employee
which falls in the range of 12 months from today.
USE [colombo]
GO
SELECT RTrim(c.LastName) + ' ' + RTrim(c.FirstName) AS Employee_name,
c.DateOfBirth,
CASE WHEN c.CurrBDate >= c.Today THEN NextBDate
ELSE c.NextBDate END AS BirthDay
FROM
(SELECT b.lastname, b.firstname, b.DateOfBirth, b.Today,
b.CurrBDate +
(CASE WHEN DAY(b.DateOfBirth)=29 AND DAY(b.CurrBDate)=28
THEN 1 ELSE 0 END) AS CurrBDate,
b.NextBDate +
(CASE WHEN DAY(b.DateOfBirth)=29 AND DAY(b.NextBDate)=28
THEN 1 ELSE 0 END) AS NextBDate
FROM
(SELECT a.lastname, a.firstname, a.DateOfBirth, a.Today,
DateAdd(Year,a.Delta,a.DateOfBirth) AS CurrBDate,
DateAdd(Year,a.Delta + 1,a.DateOfBirth) AS NextBDate
FROM
(SELECT lastname, firstname, DateOfBirth,
DateDiff(year,dateofbirth, GETDATE()) AS Delta,
CAST(Convert(CHAR(8), GETDATE(),112) AS DateTime) AS Today
FROM Employees) AS a) AS b) AS c
|
Query results will look like this:
| Employee_Name | DateOfBirth | BirthDay |
| silver john | 1958-12-08 | 2008-12-08 |
| garbo gret | 1949-01-15 | 2008-01-15 |
| chen le | 1969-10-01 | 2008-10-01 |
| defo daniel | 1978-04-01 | 2008-04-01 |
| ... | ... | ... |
|