This lab will focus on reporting on lab orders and results. medications and medication orders. We will use admissions for COPD exacerbations as a sample case, building on code from prior labs.
How many times does the term “WBC” show up in the LabComponentResultFact table? Look at the ComponentName column and use “LIKE” to identify them. The percent sign (%) is a wildcard.
SELECT count(*)
FROM deid_uf.LabComponentResultFact
WHERE ComponentName LIKE '%WBC%'How many different ways can a patient’s serum white blood cell count be measured (the WBC count measured in a patient’s blood, not any other body fluids, such as urine, CSF, etc, and not descriptions of the white blood cells themselves)? You should be able to narrow it down to a small-ish list with a query, and from that list, pick the relevant ones. Show your query for the near-final list, and then enter your final number.
There are many tests where SpecimenSource is *Unspecified, so to have a comprehensive query, I would recommend looking through all labs that
WHERE SpecimenSource = ‘Blood’
OR SpecimenSource = ‘Whole Blood’
OR SpecimenSource = ‘Serum’
OR SpecimenSource = ’*Unspecified’
OR SpecimenSouce IS NULL
SELECT DISTINCT LabComponentKey,
ComponentName
FROM deid_uf.LabTestFact
LEFT JOIN deid_uf.LabTestComponentResultMappingFact
ON LabTestFact.LabTestKey = LabTestComponentResultMappingFact.LabTestKey
LEFT JOIN deid_uf.LabComponentResultFact
ON LabComponentResultFact.LabComponentResultKey = LabTestComponentResultMappingFact.LabComponentResultKey
WHERE (ComponentName LIKE '%white blood%' OR ComponentName LIKE '%WBC%')
AND (SpecimenSource = 'Blood'
OR SpecimenSource = 'Whole Blood'
OR SpecimenSource = 'Serum'
OR SpecimenSource = '*Unspecified'
OR SpecimenSource IS NULL)## [1] 4List the top 4 most common serum WBC tests for ALL patients? If there is something in the top of your list that you dont think belongs, you can exclude it using a WHERE clause. ## Answer
SELECT TOP 4
LabComponentKey,
ComponentName,
count(*) AS N
FROM deid_uf.LabTestFact
LEFT JOIN deid_uf.LabTestComponentResultMappingFact
ON LabTestFact.LabTestKey = LabTestComponentResultMappingFact.LabTestKey
LEFT JOIN deid_uf.LabComponentResultFact
ON LabComponentResultFact.LabComponentResultKey = LabTestComponentResultMappingFact.LabComponentResultKey
WHERE (ComponentName LIKE '%white blood%' OR ComponentName LIKE '%WBC%')
AND ComponentName != 'Morphology (WBC,MISC)'
AND (SpecimenSource = 'Blood'
OR SpecimenSource = 'Whole Blood'
OR SpecimenSource = '*Unspecified')
GROUP BY LabComponentKey, ComponentName
ORDER BY N DESC
How many WBC tests (for this exercise, use LabComponentKey 994 and 6482) were done during the admissions for patient with a COPD Exacerbation (ICD-10 J44.1) since 2016?
SELECT count(*)
FROM deid_uf.EncounterFact e
LEFT JOIN deid_uf.DiagnosisTerminologyDim d ON d.DiagnosisKey = e.PrimaryDiagnosisKey
LEFT JOIN deid_uf.LabComponentResultFact r ON r.PatientDurableKey = e.PatientDurableKey
WHERE year(e.DateKeyValue) >= 2016
AND d.Type = 'ICD-10-CM'
AND d.Value = 'J44.1'
AND e.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)
AND (r.LabComponentKey = 994 OR r.LabComponentKey = 6482)
How many WBC tests were ordered for each patient above? Your result should have one row per patient per admission (the same patient may have been admitted multiple times), with one column that counts the total number of WBC that patient had.
SELECT DISTINCT e.PatientDurableKey,
e.EncounterKey,
count(e.encounterKey),
count(LabComponentKey) AS WBCNumber
FROM deid_uf.EncounterFact e
LEFT JOIN deid_uf.DiagnosisTerminologyDim d ON d.DiagnosisKey = e.PrimaryDiagnosisKey
LEFT JOIN deid_uf.LabComponentResultFact r ON r.PatientDurableKey = e.PatientDurableKey
WHERE year(e.DateKeyValue) >= 2016
AND d.Type = 'ICD-10-CM'
AND d.Value = 'J44.1'
AND e.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)
AND (r.LabComponentKey = 994 OR r.LabComponentKey = 6842)
GROUP BY e.PatientDurableKey, e.EncounterKeyGenerate a table that shows the result of all of the WBC results for the patients in the previous question, along with the units, expected ranges, if it was abnormal, and any flags
SELECT e.PatientDurableKey,
e.AdmissionDateKeyValue,
e.DischargeDateKeyValue,
r.CollectionDateKeyValue,
r.ResultDateKeyValue,
r.ProcedureName,
r.ComponentName,
r.Value,
r.Unit,
r.ReferenceValues,
r.Abnormal,
r.Flag
FROM deid_uf.EncounterFact e
LEFT JOIN deid_uf.DiagnosisTerminologyDim d ON d.DiagnosisKey = e.PrimaryDiagnosisKey
LEFT JOIN deid_uf.LabComponentResultFact r ON r.PatientDurableKey = e.PatientDurableKey
WHERE year(e.DateKeyValue) >= 2016
AND d.Type = 'ICD-10-CM'
AND d.Value = 'J44.1'
AND e.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)
AND (r.LabComponentKey = 994 OR r.LabComponentKey = 6842)
Count the number of each type of lab (using ComponentName) that was performed for patients admitted with a COPD exacerbation during their admission.
SELECT r.ComponentName, count(*) as N
FROM deid_uf.EncounterFact e
LEFT JOIN deid_uf.DiagnosisTerminologyDim d ON d.DiagnosisKey = e.PrimaryDiagnosisKey
LEFT JOIN deid_uf.LabComponentResultFact r ON r.PatientDurableKey = e.PatientDurableKey
WHERE year(e.DateKeyValue) >= 2016
AND d.Type = 'ICD-10-CM'
AND d.Value = 'J44.1'
AND e.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)
group By r.ComponentName
order by N desc
Modify the Measurements section to include at least two different lab-related measurements, and specify how they relate to your project observations in terms of a date range, encounter linkage, etc. In the exercise, the admission and discharge dates were used as the date range for lab values of interest; you’ll need to try something different if you are working with outpatient data.
Before you start coding, modify your Table 1 design to include rows for the two or more lab-related measurements. Decide whether you want to dichotomize the lab results (e.g., “LDL > 130 mg/dl, n(%)”) or present the mean+-SD or median IQR, or both, etc.
Refine your Table 1 SQL/R/Stata code to obtain the results needed to fill in the new lab-related rows in your Table 1.