Skip to Content

Greetings! My name is Garrett Fitzgerald, and I work at Penobscot Community Health Care, a Federally Qualified Health Center in Bangor, Maine. We use GE’s Centricity practice management and medical records systems, and I use various tools to access the data — one of the most useful of which is Crystal Reports 2008.

One thing I commonly need to do is to list all patients, but call out a specific diagnosis that some of them have. If I were to add the tables in the database expert, even if I were to use a Left Join to get all the patients, I would lose them as soon as I tried to use the Record Selection criteria to get the specific diagnoses I needed.

My solution to this is to use a SQL Command. When I go into the Database Expert and select my Connection, instead of choosing the tables, I can use “Add Command”, and enter the SQL query directly.

A typical SQL query would look like this:

SELECT Person.pId, Person.firstName AS patFirst, Person.lastName AS patLast
        , Problem.onsetDate as refusedDate
    FROM Person
        LEFT JOIN Problem ON Person.pId = Problem.pId
    WHERE Problem.code BETWEEN 'ICD-V64.02' AND 'ICD-V64.08'

However, this gives us the same problem as I described above — the list of Persons is restricted to those who have refused vaccination. To get around this, we can move the WHERE criteria into the JOIN clause:

SELECT Person.pId, Person.firstName AS patFirst, Person.lastName AS patLast
        , Problem.onsetDate as refusedDate
    FROM Person
        LEFT JOIN Problem ON Person.pId = Problem.pId
            AND Problem.code BETWEEN 'ICD-V64.02' AND 'ICD-V64.08'

This gives us all patients as desired, with either the date they refused vaccination or NULL if they didn’t.

Next time, I’ll show how I broke down the individual vaccination observations to make it easy to monitor compliance.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply