UNI/CARE - Pro-Filer™ Reporting: Finding a record that was in effect at the time of service.
>> Sunday, March 28, 2010
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)
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
3 comments:
Nice idea with the blog. I'll make sure I stop by from time to time.
Wouldn't this query pull in multiple GAFs for older services? Also, if providers just expire and unexpire the same score (as they sometimes do) you might only be able to use the query for services in real time rather than as a historical account.
Good question. If the providers are expiring the older GAF scores before creating new ones so that at any point in time the client only has a single Axis5, then our where clause would work fine. In a system where GAFs overlap each other, this would cause a both a reporting problem as well as a clinical problem.
GW
One other thing I should have mentioned, this is where the value of Quality Assurance reports come in. In this case, we would want a QA report that shows that the Axis5 is continuous and that no GAF scores overlap each other.
GW
Post a Comment