How to: Calculate Current Age of Members

Age is an important measure in healthcare. We need to calculate the right age to differentiate aged or non-eldly enrolled members. Moreover Age is an essential part of conditions in HEDIS measures. However, it is very easy to get it skewed by birthday or some special case, such as leap years. Here you can find out the solutions by using TSQL or SSIS.

Before we go to the right answers, what do you think of the following two solutions?

• DATEDIFF(“YYYY”, BirthDate, GETDATE())
• DATEDIFF(“DD”,BirthDate, GETDATE()) / 365

As the first glance, they may look right. Now suppose one child was born at 2012-11-15, when you try to use the following TSQL to get his age

select DATEDIFF(YY, '2012-11-15',  '2013-10-15')

You will get 1 as the result. The year has been rounding to one although the first birthday has not arrived yet.

TSQL Solution 

In the case that you need to derive a column for the members' age, use the following:

Case
    when (MONTH([MEMBER_BIRTH_DATE]) > MONTH(CURRENT_TIMESTAMP))
                  or ((MONTH([MEMBER_BIRTH_DATE]) = MONTH(CURRENT_TIMESTAMP)) and (DAY([MEMBER_BIRTH_DATE]) > DAY(CURRENT_TIMESTAMP)))
                  then DATEDIFF(YY, [MEMBER_BIRTH_DATE], CURRENT_TIMESTAMP) - 1
                else DATEDIFF(YY, [MEMBER_BIRTH_DATE], CURRENT_TIMESTAMP)
           end MEMBER_AGE

SSIS  Solution

SSIS expression for derived column task is:

DATEDIFF("Year",MEMBER_BIRTH_DATE,GETDATE()) - ((MONTH(MEMBER_BIRTH_DATE) == MONTH(GETDATE()) && DAY(MEMBER_BIRTH_DATE) > DAY(GETDATE())) || MONTH(MEMBER_BIRTH_DATE) > MONTH(GETDATE()) ? 1 : 0)

Isn't it easy?