# Performing a Right and Comprehensive Age Calculation

• Sergiy - Friday, March 31, 2017 6:43 AM

ChrisM@Work - Friday, March 31, 2017 6:05 AM

Our results agree for October, but not September, which is why I chose it for the example.
Try DOB of 2017-09-17 and "asof" date of 2017-11-16.
In fact, try 2017-09-01 to 2017-09-17 with the same As Of date.
Also, for a DOB of 2017-09-30 with the same As Of date, I think the age would be one month and 16 days: October, plus 16 days.

OK,
for DOB 2017-09-17 the subject reaches the age of 1 month on 2017-10-17.
2nd full month would be reached on 2017-11-17.
But the "asof" date is 2017-11-16, so we have not reached 2 moths of age.
Therefore we get the age of 1 month and the number of days from the beginning of 2nd month (2017-10-17) and the "asof" date - 30 days.

For DOB of 2017-09-30 the fist month of age ends on 2017-10-30, and again, the 2nd month of age is never reached.
So, the age is 1 month + number of days between 2017-10-30 and 2017-11-16, which is 17 days.

Number of days in September or October is irrelevant for the age calculations.

Here are 3 problems to resolve:
1. DOB = 2017-02-28, ASOF date = 2017-03-28, Age (Months, Days) = ?.
2. DOB = 2017-03-28, ASOF date = 2017-04-01, Age (Months, Days) = ?.
3. DOB = 2017-02-28, ASOF date = 2017-04-01, Age (Months, Days) = ?.

Verification key:
Ages calculated in problems 1 and 2 must add up to the age calculated in the problem 3.

Got it Sergiy, thanks

1. DOB = 2017-02-28, ASOF date = 2017-03-28, Age (Months, Days) = 1,0

2. DOB = 2017-03-28, ASOF date = 2017-04-01, Age (Months, Days) = 0,4

3. DOB = 2017-02-28, ASOF date = 2017-04-01, Age (Months, Days) = 1,4

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• NBSteve - Friday, March 31, 2017 7:02 AM

ChrisM@Work - Friday, March 31, 2017 6:05 AM

Sergiy - Friday, March 31, 2017 5:29 AM

My method does not return the number of days I November or any other calendar month.It returns the number of days from the beginning of the current, incomplete month of the age.In your example, the full month from 2017-10-17 would be completed on 2017-11-17. On 2017-11-16 it's still 0 years and 0 months of age, so we count all 30 days which passed since 2017-10-17.

Our results agree for October, but not September, which is why I chose it for the example.
Try DOB of 2017-09-17 and "asof" date of 2017-11-16.
In fact, try 2017-09-01 to 2017-09-17 with the same As Of date.
Also, for a DOB of 2017-09-30 with the same As Of date, I think the age would be one month and 16 days: October, plus 16 days.

The key to Sergiy's requirement is that you only start counting the days from the point at which you stop counting months, the monthly anniversary so to speak.  For any given start day, first you have to find the last monthly anniversary (even if the day doesn't exist), and then start counting days from there.

My method is closer to this and it's incorrect. We agree on years and months, remember.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw