select pid, title from lists where diagnosis="ICD10:E11.9";
// List of patients with diabetes diagnosis 568 patients
select patient_data.ss as seqn, patient_data.fname, patient_data.lname ,
convert(procedure_result.result,decimal(10,2)) as a1c
FROM procedure_result,procedure_report,procedure_order,patient_data
WHERE procedure_result.procedure_report_id=procedure_report.procedure_report_id
AND procedure_report.procedure_order_id=procedure_order.procedure_order_id
AND patient_data.pid = procedure_order.patient_id
AND procedure_result.result_code="4548-4"
AND convert(procedure_result.result,decimal(10,2)) > 9
AND (patient_data.pid in (select pid from lists where diagnosis="ICD10:E11.9") );
// Of these patients who’s a1c levels were 9.0 or higher (85 patients)
select patient_data.ss as seqn, patient_data.fname, patient_data.lname
,convert(procedure_result.result,decimal(10,2)) as a1c
FROM procedure_result,procedure_report,procedure_order,patient_data
WHERE procedure_result.procedure_report_id=procedure_report.procedure_report_id
AND procedure_report.procedure_order_id=procedure_order.procedure_order_id
AND patient_data.pid = procedure_order.patient_id
AND procedure_result.result_code="4548-4"
AND convert(procedure_result.result,decimal(10,2)) > 9
AND (patient_data.pid NOT IN (select pid from lists where diagnosis="ICD10:E11.9") )
// Then who didn’t have a diagnosis of diabetes listed, but does have a1c > 9
// 55 patients
Very interesting but perhaps not surprising. 86 million Americans over the age of 20 have pre-diabetes and if not checked every few years, when they surface they will have high blood sugars and elevated A1c’s. I wonder if their degree of obesity, hyperlipidemia and hypertension is any less than those who have been told they are diabetic and hence have probably had the disease longer
There are 55 patients with a1c > 9, but “hadn’t been told before that they had diabetes.” Out of the 9364 patients, 55/9364 works out to a little less than 6%.
So our results seem comparable to the CDC’s epidemiological results.
BTW, it seems odd that we have total cholesterols and HDLs but I don’t see any LDLs which presents a problem. Did we not load the TRIGLY G table that had LDLs and triglyceride data? Similarly, I don’t see glucose. From the original spreadsheet here are are tables I thought we were going to load below. Did patients not have all of the tests run? Seems like most patients have 11 test results (including grip test)
The NHANES data itself does not have complete lab results for all patients/all tests.
For trigylcerides, which includes the LDL
https://wwwn.cdc.gov/Nchs/Nhanes/2011-2012/TRIGLY_G.htm
The CDC has only 3239 rows of data in total, with a number of them missing
Thanks, that’s helpful but strange from the standpoint you would think everyone had every test. Maybe due to financial constraints they did not. Sorry we have so many holes in the lab data. What about the following
PAQ706
PAQ610
PAD680
ALQ130
SMQ040
DUQ240
RXDCOUNT
RXDRUG
RXDDRGID
HIQ011
HIQ031B
HIQ031D
Alcohol questionnaire has 5000+ rows of data, 3334 have responses, with the rest missing.
Smoking also has around 5000 valid answers.
Drug use has around 3000 valid responses.
How to interpret the quality of the prescription drug info provided by NHANES is a little trickier, since there are multiple rows per respondent. (for multiple drugs)
But there are rough 5700 who answered “no” to taking prescriptions meds, who would have empty med lists.
In LibreHealth where is the physical activity results located? I am still seeing empty results for smoking under History >> lifestyle. Is the data stored somewhere else? Also, not seeing lab ranges. Where is race and income stored in the EHR? Thanks
I haven’t updated the public server yet which is why race/income (viewable under demographics on the Social Statistics Tab of Demographics) isn’t there yet. Similarly with the Lab Ranges, (haven’t updated the public server).
The lifestyle data, I still need to take care of though. (oops
I have downloaded the SQL files that were on the server as of last night, (including any “enhanced content” that was created at that point.) I will fill in the missing data based on that, and will update the server when ready.
Just created some content for Bettie Huang as our first complicated geriatric patient #6226. Surprisingly easy to find abnormal images and EKGs under Google Images
Added a bunch of common drugs to the drug drop down list (using medication list).
Reminds me how horribly we have neglected ECG data in the EHR. We don’t even have a minimal EKG results form, much less a little tool for marking and calculating QT intervals and the like.
public function test( PQRSPatient $patient, $beginDate, $endDate )
{
$query =
"SELECT COUNT(b1.code) AS count ".
" FROM billing AS b1 ".
" INNER JOIN billing AS b2 ON (b1.pid = b2.pid) ".
" JOIN form_encounter AS fe ON (b1.encounter = fe.encounter) ".
" JOIN patient_data AS p ON (b1.pid = p.pid) ".
" INNER JOIN pqrs_efcc1 AS codelist_b ON (b1.code = codelist_b.code)".
" INNER JOIN pqrs_efcc1 AS codelist_a ON (b2.code = codelist_a.code)".
" WHERE b1.pid = ? ".
" AND fe.provider_id = '".$this->_reportOptions['provider']."'".
" AND fe.date >= '".$beginDate."' ".
" AND fe.date <= '".$endDate."' ".
" AND TIMESTAMPDIFF(YEAR,p.DOB,fe.date) BETWEEN '18' AND '75' ". //age must be between 18 and 75 on the date of treatment
" AND (b1.code = codelist_b.code AND codelist_b.type = 'pqrs_0001_b') ".
" AND (b2.code = codelist_a.code AND codelist_a.type = 'pqrs_0001_a') ;";
$result = sqlFetchArray(sqlStatementNoLog($query, array($patient->id)));
if ($result['count'] > 0){
return true;} else {return false;}
}
Then:
public function test( PQRSPatient $patient, $beginDate, $endDate )
{
$query =
“SELECT COUNT(b1.code) AS count “.
” FROM billing AS b1 “.
” JOIN form_encounter AS fe ON (b1.encounter = fe.encounter) “.
” JOIN patient_data AS p ON (b1.pid = p.pid) “.
” WHERE b1.pid = ? “.
” AND fe.date >= '”.$beginDate."’ “.
” AND fe.date <= ‘".$endDate."’ “.
” AND b1.code = ‘G9687’) ;";
$result = sqlFetchArray(sqlStatementNoLog($query, array($patient->id)));
if ($result['count'] > 0){
return false;} else {return true;}
//inverse count. If find code, it is a denom exclude.
}
Then:
public function test( PQRSPatient $patient, $beginDate, $endDate )
{
//inverse measure
$query =
“SELECT COUNT(b1.code) as count “.
” FROM billing AS b1 “.
” JOIN form_encounter AS fe ON (b1.encounter = fe.encounter)”.
" WHERE b1.pid = ? “.
” AND fe.date BETWEEN ‘".$beginDate."’ AND ‘".$endDate."’ “.
” AND b1.code = ‘3046F’;";
//above code can be with 8P.
//Performance NOT MET is 3044F and 3045F.
//These need to be a return value (for new engine).
$result = sqlFetchArray(sqlStatementNoLog($query, array($patient->id)));
if ($result[‘count’] > 0){ return true;} else {return false;}
}
Sorry…tried to upload a file that has the ~350 diabetes related Dx and Tx codes in sql format that would allow the above queries to run for the “codelist” joins above, but this forum only allows image files…sad…
Anyway, there are a LOT of diagnosis codes to be looking for…