I am using crystal in a healthcare setting and I need to count all the patients that have both Diabetes (250.00 to 250.93) and CHF (428.0 to 428.9). The only way to run this data is on ICD-9 codes (listed above). However, when I enter that criteria in crystal I get zero patients. Doing a small sample of Diabetes patients, I manually counted 10 patients that have CHF, but I need 3 years worth of data. Any suggestions?
dobailey1 (BOB member since 2008-09-22)
There is no easy way to get this data using just one “diagnosis” table (I’m assuming you have a “patient” master table and a “diagnosis” child table.
You could set the filter with {diagnosis.ICD9} between 250.00 and 250.93 or {diagnosis.ICD9} between 428.0 and 428.9. However that will also get you patients that only have one or the other.
So, there are two ways that you can do this. If you can write SQL, I would recommend using a Command instead of the tables. It will look something like this:
Select <fields>
from patient
inner join diagnosis on diagnosis.patient_key = patient.patient_key
where diagnosis.ICD9 between 250.00 and 250.93
and exists
(Select 'X'
from diagnosis as d1
where d1.patient_key = patient.patient_key
and d1.ICD9 between 428.0 and 428.9)If you’re not comfortable writing SQL, you would do this:
In the Database Expert, add a second copy of the diagnosis table. When you try to add a table that’s already there, Crystal will automatically ask you if you want to alias it - select Yes. The new copy of the table will be named something like “diagnosis_1”.
Link from the patient table to diagnosis_1 on your patient key. Make sure that the joins to diagnosis and diagnosis_1 are both Inner joins.
In the Select Expert, add the following conditions:
Because of the inner joins to the two copies of the diagnosis table, you’ll only get those patients that have both diagnoses.
-Dell
hilfy 
(BOB member since 2007-04-16)