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

Read more...

UNI/CARE - Pro-Filer™ Reporting: Finding a record that was in effect at the time of service.

Normally when we are writing reports in Pro-Filer™ we want to see the value of a field at some particular time. Usually we are relating to the recorded service. For example, in a recent report request, I was asked to provide a list of services with the client’s GAF (Axis5) that was effective at the time of service.

How do we go about this? First of all, I’m not going to give you the entire query or Crystal Report, just the bits of code (Snippets) that define our service and GAF score. I keep a text file with a bunch of these snippets that I can refer to and just copy and paste code as needed.

So, let’s grab our recorded service. The start of the service is the STARTTIME field in the RECORDED_SERVICE table.

     RECORDED_SERVICE.STARTTIME

Now, let’s make sure that our GAF score was EFFECTIVE on or before the RECORDED_SERVICE.STARTTIME. This information is stored in the ClientAxis5 table NOT the Axis5 table. So our query starts like this:

     WHERE ClientAxis5.EffDate <= RECORDED_SERVICE.STARTTIME

This is only part of the solution however, now we have to filter out all those records that were expired previous to the recorded service. So we examine the ClientAxis5 expiration date for that. Since there may be new GAF scores that were entered after our recorded service, we have to find the one that was not expired at the time. This is how our query will look:

     WHERE ClientAxis5.EffDate <= RECORDED_SERVICE.STARTTIME
     AND (ClientAxis5.ExpDate IS NULL OR ClientAxis5.ExpDate >=
     RECORDED_SERVICE.STARTTIME)

Pay close attention to the parentheses around the second line. They are critical. So now we have a query that is first checking to be sure the GAF was entered into the system prior to the service and we are checking to be sure that the expiration date is null or was expired after our recorded service.

This query can be used throughout the Pro-Filer™ database for various other fields as well. Cost Center Locations, Client Demographics and just about anything else you would want to know.

Let me know if you have any questions or comments. Until next time…

GW

Read more...

Recommendation: Shawn Frasier CEO Sitewerks Technologies Recommends Gary W. Clark

“Working with Gary was always a pleasure. He approaches each technical and design challenge with enthusiasm, creativity, good humor and extensive real-world experience. Whether it's a recalcitrant and heretofore unmet reporting challenge, or an SQL programming puzzle, Gary approaches it all with one thing in mind: Providing the customer with the best, fastest and most creative approach possible.”

Shawn Frasier

Read more...