UNI/CARE Pro-Filer™ Reporting: Retrieving a Client's Age on the Day of Service from Their Date of Birth

One of the most common tasks handed to the Pro-Filer™ analyst is producing reports based on the age of the client at the time of service.  Since age is not a field within the database, (it really can't be as it is in continuous flux) this value must be calculated.  We could certainly add a formula to each of our reports or queries to do this, but let's write the code just once and then reference it when needed.  For this purpose we are going to add a user function in SQL to our reporting server.

Since we have the client's birth date stored in:

    Client.BDate

We can use a function to return their age by comparing to whatever date we are measuring against.  In this case let's use the recorded service start time which is stored in:

    RECORDED_SERVICE.STARTTIME

So we create our function with the code below:

begin sql----------------------------------------------->

create function dbo.fn_GetAge
(
      @pDateOfBirth datetime
    , @pAsOfDate datetime
)
returns int
as
begin

declare @vAge int

    if @pDateOfBirth >= @pAsOfDate
     
       return 0

    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

return @vAge
end
go

<-------------------------------------------------end sql

DESCRIPTION
Our function as created accepts two inputs, the date of birth and the comparison date. 

The first if statement is just an error check to ensure that the date of birth is prior to our second date.  If not, it returns a 0.

The set statement starts the process of computing the age for us.  We can't just use a straight datediff function without the remaining if clause however.  Datediff simply returns the number of years between two dates.  So, if today is April 11, 2010 without checking for date, any birth date in 1990 would return an age of 20 even if the birth date has not yet passed for 2010.

USAGE
Let's create a list of clients in the system that were under age 5 at the time of service.  In this case we want the earliest age at which they received services:

begin sql----------------------------------------------->

select

      c.lname + ', ' + c.fname as ClientName

    , c.id as ClientID

    , min(dbo.fn_GetAge (c.bdate, rs.starttime)) as ClientAge

from

               recorded_service_helper rsh

      inner join client c on rsh.client_oid = c.oid

      inner join recorded_service rs on rsh.recorded_service_oid = rs.oid

where       dbo.fn_GetAge (c.bdate, rs.starttime) <= 5
        and rs.recorded_service_voided_moniker is null
        and rs.service_status_moniker is null

group by c.id,c.lname,c.fname

<-------------------------------------------------end sql

Hopefully this will help make your job just a wee-bit easier.

Til next time...

GW