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.
