I've been trouble with doing query on calculate age base on minimum effective date for each employee. How would I do this? Please help and thank you in advance.
Here's my query and don't know how to get age calculate yet
select a.EMPLOYEE, a.LAST_NAME, a.FIRST_NAME, b.BIRTHDATE, c.StartDate, c.PLAN_CODE,
from EMPLOYEE a
right outer join PAEMPLOYEE b on a. EMPLOYEE = b.EMPLOYEE
right outer join (Select distinct EMPLOYEE, min(START_DATE) as StartDate, PLAN_CODE, SMOKER from BENEFIT
where PLAN_CODE in ('GAP1', 'CI01', 'CI21')
GROUP BY EMPLOYEE, PLAN_CODE, SMOKER)c
on a.EMPLOYEE = c.EMPLOYEE
order by EMPLOYEE, StartDate
Emp# LName FName BirthDate StartDate PlanCode
100 Warman James 1955-11-25 2014-01-01 GAP1
100 Warman James 1955-11-25 2014-01-01 CI21
101 Strickland James 1956-10-08 2015-01-01 CI01
101 Strickland James 1956-10-08 2015-01-01 CI21
AND SO ON...
That depends on specifically how you want to treat a leap year.
Does a birthday of Feb 29 mean a person is a year older in non-leap years on Feb 28 or Mar 1? That is the trickiest issue when dealing with age. For some purposes, such as retirement calcs, laws can even require one method or the other.
CREATE FUNCTION [dbo].[fnAge]
( @pDateOfBirth DATETIME, @pAsOfDate DATETIME
DECLARE @vAge INT
IF @pDateOfBirth >= @pAsOfDate
SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
(MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge - 1