-- Investigating LabDim
SELECT count(*)
FROM LabDim

SELECT TOP 100 *
FROM deid_uf.LabDim

SELECT IsCurrent,
       count(*)
FROM deid_uf.LabDim
GROUP BY IsCurrent

-- Investigating LabComponentDim
SELECT count(*)
FROM LabComponentDim

SELECT TOP 100 *
FROM LabComponentDim


SELECT TOP 100 *
FROM LabComponentDim
WHERE NAME LIKE '%wormwood%'

-- LEFT JOIN and WHERE Clause
SELECT *
FROM deid_uf.LabComponentResultFact
	     LEFT JOIN LabComponentDim ON LabComponentDim.LabComponentKey = LabComponentResultFact.LabComponentKey
WHERE LabComponentDim.Name LIKE '%wormwood%'

-- INNER JOIN with CONDITIONS in the join
SELECT *
FROM deid_uf.LabComponentResultFact
	     INNER JOIN LabComponentDim ON LabComponentDim.LabComponentKey = LabComponentResultFact.LabComponentKey
		                                   AND LabComponentDim.Name LIKE '%wormwood%'


-- How many distinct LOINC Codes are there?
SELECT count(DISTINCT LoincCode)
FROM deid_uf.LabComponentDim

SELECT LoincCode,
       count(*) AS N
FROM deid_uf.LabComponentDim
GROUP BY LoincCode
ORDER BY N DESC

SELECT Type,
       count(*) AS N
FROM deid_uf.LabComponentDim
GROUP BY Type

SELECT *
FROM deid_uf.LabComponentDim
WHERE Type = 'Lab Standard'
  AND name LIKE '%creatinine%'

SELECT Subtype,
       count(*) AS N
FROM deid_uf.LabComponentDim
GROUP BY Subtype

-- Lets evaluate the differences between LabTestFact and LabComponentResultFact

SELECT TOP 100 *
FROM deid_uf.LabComponentResultFact
WHERE PatientDurableKey <> '-1'

SELECT TOP 100 *
FROM deid_uf.LabTestFact

SELECT TOP 100
           PatientDurableKey,
           LabTestKey,
           ProcedureKey,
           ProcedureName,
           EncounterKey,
           ProblemComboKey,
           OrderedDateKey,
           OrderedDateKeyValue,
           OrderedInstant,
           CollectedDateKey,
           CollectedDateKeyValue,
           CollectedInstant,
           FirstReceivedDateKey,
           FirstReceivedDateKeyValue,
           FirstReceivedInstant,
           FirstFinalVerifiedDateKey,
           FirstFinalVerifiedDateKeyValue,
           CultureLastResultedDateKey,
           CultureLastResultedDateKeyValue,
           CultureLastResultedInstant,
           SpecimenSource,
           TestName,
           OrderPriority,
           SourceType,
           AbnormalLevel,
           VerificationStatus,
           IsAbnormal,
           IsCritical,
           IsCorrection
FROM deid_uf.LabTestFact

SELECT OrderPriority,
       count(*) AS N
FROM deid_uf.LabTestFact
GROUP BY OrderPriority
ORDER BY N DESC


SELECT SourceType,
       count(*) AS N
FROM deid_uf.LabTestFact
GROUP BY SourceType
ORDER BY N DESC

SELECT SpecimenSource,
       count(*) AS N
FROM deid_uf.LabTestFact
GROUP BY SpecimenSource
ORDER BY N DESC

SELECT SpecimenSource,
       Laboratory,
       count(*) AS N
FROM deid_uf.LabTestFact
WHERE SpecimenSource = '*Unspecified'
GROUP BY SpecimenSource, Laboratory
ORDER BY N DESC

SELECT year(FirstFinalVerifiedDateKeyValue),
       Laboratory,
       count(*) AS N
FROM deid_uf.LabTestFact
WHERE SpecimenSource = '*Unspecified'
GROUP BY year(FirstFinalVerifiedDateKeyValue), Laboratory
ORDER BY year(FirstFinalVerifiedDateKeyValue) DESC


SELECT *
FROM deid_uf.LabTestFact
WHERE SpecimenSource = '*Unspecified'
  AND year(FirstFinalVerifiedDateKeyValue) = 2020

-- How to get from LabTestFact to LabComponentResultFact?
-- What is the common key?
SELECT TOP 100 *
FROM deid_uf.LabTestFact

SELECT TOP 100 *
FROM deid_uf.LabComponentResultFact

SELECT TOP 100 *
FROM LabTestComponentResultMappingFact

-- What are the most common lab components?
SELECT LabComponentKey,
       ComponentName,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
GROUP BY LabComponentKey, ComponentName
ORDER BY N DESC


-- Lets look at how many orders this patient had:
SELECT *
FROM deid_uf.LabTestFact
WHERE PatientDurableKey = 'DA92726394332A'

-- Now lets see how many lab results they had
SELECT *
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 LabTestFact.PatientDurableKey = 'DA92726394332A'

SELECT LabComponentResultFact.ComponentName,
       LabComponentResultFact.Value
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 LabTestFact.PatientDurableKey = 'DA92726394332A'

SELECT LabComponentResultFact.ResultDateKeyValue,
       TestName,
       ComponentName,
       Value
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 LabTestFact.PatientDurableKey = 'DA92726394332A'
ORDER BY ResultDateKeyValue, TestName

-- Lets dig into an individual lab and create a grouper
SELECT *
FROM deid_uf.LabComponentResultFact
WHERE ComponentName LIKE '%egfr'

-- Lets look at how frequently each lab is used/reported to guide our query
SELECT LabComponentKey,
       ComponentName,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
WHERE ComponentName LIKE '%egfr%'
GROUP BY LabComponentKey, ComponentName
ORDER BY N DESC

-- I want to learn more about CREAT WITH eGFR...is that a creatinine or an eGFR? or both?
SELECT ResultDateKeyValue,
       Value,
       ComponentName
FROM deid_uf.LabComponentResultFact
WHERE ComponentName = 'CREAT WITH eGFR'

-- How many unique values are there in this column?
SELECT count(DISTINCT Value)
FROM deid_uf.LabComponentResultFact
WHERE ComponentName = 'CREAT WITH eGFR'

-- What do the non-missing data look like?
SELECT Value
FROM deid_uf.LabComponentResultFact
WHERE ComponentName = 'CREAT WITH eGFR'
  AND VALUE != ''

-- Lets verify by looking at the opposite query
SELECT count(*)
FROM deid_uf.LabComponentResultFact
WHERE ComponentName = 'CREAT WITH eGFR'
  AND VALUE = ''


SELECT LabComponentKey,
       ComponentName,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
WHERE ComponentName LIKE '%egfr%'
GROUP BY LabComponentKey, ComponentName
ORDER BY N DESC

-- LabComponentKey 595, 594

SELECT *
FROM deid_uf.LabComponentResultFact
WHERE LabComponentKey = 594
   OR LabComponentKey = 595

SELECT FLAG,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
WHERE LabComponentKey = 594
   OR LabComponentKey = 595
GROUP BY Flag

SELECT Abnormal,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
WHERE LabComponentKey = 594
   OR LabComponentKey = 595
GROUP BY Abnormal

-- Create a new temp table that will hold your LabComponentKeys of interest
CREATE TABLE #MyLabs
(
	id              INT PRIMARY KEY,
	LabComponentKey INT
)

-- Populate your empty table with values
INSERT INTO #Mylabs
VALUES (1, 594),
       (2, 595)

-- Test to see what your table looks like
SELECT *
FROM #MyLabs

-- Use your new temp table in a join to select the labs of interest
SELECT *
FROM #MyLabs
	     LEFT JOIN deid_uf.LabComponentResultFact ON LabComponentResultFact.LabComponentKey = #MyLabs.LabComponentKey

-- remove/delete your temporary table
DROP TABLE #MyLabs

-- How quickly are STAT CBC and INR Orders done?
-- First start by finding relevant labs
-- Lets look at all labs to see the most common
SELECT LabComponentKey,
       ComponentName,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
GROUP BY LabComponentKey, ComponentName
ORDER BY N DESC

-- 5346, 759

SELECT LabComponentKey,
       ComponentName,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
WHERE ComponentName LIKE '%hematocrit%'
   OR ComponentName LIKE '%hct%'
GROUP BY LabComponentKey, ComponentName
ORDER BY N DESC

-- add 2892, 30941, 30939

-- lets look for INR
SELECT LabComponentKey,
       ComponentName,
       count(*) AS N
FROM deid_uf.LabComponentResultFact
WHERE ComponentName LIKE '%inr%'
   OR ComponentName LIKE '%international%'
GROUP BY LabComponentKey, ComponentName
ORDER BY N DESC

-- add 5261, 5799


CREATE TABLE #MyStat
(
	id              INT PRIMARY KEY,
	LabComponentKey INT
)

-- Populate your empty table with values
INSERT INTO #MyStat
VALUES (1, 5346),
       (2, 759),
       (3, 5261),
       (4, 5799),
       (5, 2892),
       (6, 30941),
       (7, 30939)


SELECT r.LabComponentKey,
       r.ComponentName,
       count(*) AS N
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
GROUP BY r.LabComponentKey, r.ComponentName
ORDER BY ComponentName

-- Now lets add a join to the mapping table and check our work
SELECT r.LabComponentKey,
       r.ComponentName,
       count(*) AS N
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN LabTestComponentResultMappingFact M ON M.LabComponentResultKey = r.LabComponentResultKey
GROUP BY r.LabComponentKey, r.ComponentName
ORDER BY ComponentName

-- and add the join to the labtestfact table and check our work again
SELECT r.LabComponentKey,
       r.ComponentName,
       count(*) AS N
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN LabTestComponentResultMappingFact M ON M.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN LabTestFact t ON t.LabTestKey = m.LabTestKey
GROUP BY r.LabComponentKey, r.ComponentName
ORDER BY ComponentName

-- at this point we have joined LabComponentResultFact table, which shows our lab results, with the LabTestFact table,
-- which shows data about the test ordering, using the mapping table as a bridge table
-- and we've limited the results to just the labs in our custom grouper, created as a temp table

-- Now we can start picking some columns of interest
SELECT r.LabComponentResultKey,
       r.LabComponentKey,
       r.ComponentName,
       t.Laboratory,
       t.OrderPriority,
       r.Value,
       r.Flag,
       r.Abnormal,
       r.OrderedDateKeyValue,
       r.OrderedTimeOfDayKeyValue,
       r.CollectionDateKeyValue,
       r.CollectionTimeOfDayKeyValue,
       r.ResultDateKeyValue,
       r.ResultTimeOfDayKeyValue
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN deid_uf.LabTestComponentResultMappingFact m ON m.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN deid_uf.LabTestFact t ON t.LabTestKey = m.LabTestKey
WHERE OrderPriority = 'STAT'
  AND year(r.OrderedDateKeyValue) > 2012


-- How many different labs do we have? for these tests?
SELECT t.Laboratory,
       count(*) AS N
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN deid_uf.LabTestComponentResultMappingFact m ON m.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN deid_uf.LabTestFact t ON t.LabTestKey = m.LabTestKey
WHERE OrderPriority = 'STAT'
  AND year(r.OrderedDateKeyValue) > 2012
GROUP BY t.Laboratory

SELECT r.LabComponentResultKey,
       r.LabComponentKey,
       r.ComponentName,
       t.Laboratory,
       t.OrderPriority,
       r.Value,
       r.Flag,
       r.Abnormal,
       r.OrderedDateKeyValue,
       r.OrderedTimeOfDayKeyValue,
       r.CollectionDateKeyValue,
       r.CollectionTimeOfDayKeyValue,
       r.ResultDateKeyValue,
       r.ResultTimeOfDayKeyValue
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN deid_uf.LabTestComponentResultMappingFact m ON m.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN deid_uf.LabTestFact t ON t.LabTestKey = m.LabTestKey
WHERE OrderPriority = 'STAT'
  AND year(r.OrderedDateKeyValue) > 2012
  AND t.Laboratory IN ('UCSF LAB', 'UCSF MISSION BAY LAB', 'UCSF BLOOD GAS LAB', 'UCSF MOUNT ZION CLINICAL LABS')


SELECT r.LabComponentResultKey,
       r.LabComponentKey,
       r.ComponentName,
       t.Laboratory,
       t.OrderPriority,
       r.Value,
       r.Flag,
       r.Abnormal,
       r.CollectionDateKeyValue,
       r.CollectionTimeOfDayKeyValue,
       r.ResultDateKeyValue,
       r.ResultTimeOfDayKeyValue
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN deid_uf.LabTestComponentResultMappingFact m ON m.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN deid_uf.LabTestFact t ON t.LabTestKey = m.LabTestKey
WHERE OrderPriority = 'STAT'
  AND year(r.OrderedDateKeyValue) > 2012
  AND t.Laboratory IN ('UCSF LAB', 'UCSF MISSION BAY LAB', 'UCSF BLOOD GAS LAB', 'UCSF MOUNT ZION CLINICAL LABS')


SELECT r.LabComponentResultKey,
       r.LabComponentKey,
       r.ComponentName,
       t.Laboratory,
       t.OrderPriority,
       r.Value,
       r.Flag,
       r.Abnormal,
       r.CollectionDateKeyValue,
       cast(r.CollectionDateKeyValue AS DATETIME)      AS CollectionDate,
       cast(r.CollectionTimeOfDayKeyValue AS DATETIME) AS ColletionTime,
       r.ResultDateKeyValue,
       r.ResultTimeOfDayKeyValue
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN deid_uf.LabTestComponentResultMappingFact m ON m.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN deid_uf.LabTestFact t ON t.LabTestKey = m.LabTestKey
WHERE OrderPriority = 'STAT'
  AND year(r.OrderedDateKeyValue) > 2012
  AND t.Laboratory IN ('UCSF LAB', 'UCSF MISSION BAY LAB', 'UCSF BLOOD GAS LAB', 'UCSF MOUNT ZION CLINICAL LABS')


SELECT r.LabComponentResultKey,
       r.LabComponentKey,
       r.ComponentName,
       t.Laboratory,
       t.OrderPriority,
       r.Value,
       r.Flag,
       r.Abnormal,
       cast(r.CollectionDateKeyValue AS DATETIME) + cast(r.CollectionTimeOfDayKeyValue AS DATETIME)   AS CollectionDateTime,
       cast(r.ResultDateKeyValue AS DATETIME) +    cast(r.ResultTimeOfDayKeyValue AS DATETIME)  AS ResultDateTime,
       DATEDIFF(MINUTE,
                (cast(r.CollectionDateKeyValue AS DATETIME) + cast(r.CollectionTimeOfDayKeyValue AS DATETIME)),
                (cast(r.ResultDateKeyValue AS DATETIME) + cast(r.ResultTimeOfDayKeyValue AS DATETIME))) AS ResultDelay
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN deid_uf.LabTestComponentResultMappingFact m ON m.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN deid_uf.LabTestFact t ON t.LabTestKey = m.LabTestKey
WHERE OrderPriority = 'STAT'
  AND year(r.OrderedDateKeyValue) > 2012
  AND t.Laboratory IN ('UCSF LAB', 'UCSF MISSION BAY LAB', 'UCSF BLOOD GAS LAB', 'UCSF MOUNT ZION CLINICAL LABS')


-- Insert into a temporary table
SELECT r.LabComponentResultKey,
       r.LabComponentKey,
       r.ComponentName,
       t.Laboratory,
       t.OrderPriority,
       r.Value,
       r.Flag,
       r.Abnormal,
       cast(r.CollectionDateKeyValue AS DATETIME) +
       cast(r.CollectionTimeOfDayKeyValue AS DATETIME)                                                  AS CollectionDateTime,
       cast(r.ResultDateKeyValue AS DATETIME) +
       cast(r.ResultTimeOfDayKeyValue AS DATETIME)                                                      AS ResultDateTime,
       DATEDIFF(MINUTE,
                (cast(r.CollectionDateKeyValue AS DATETIME) + cast(r.CollectionTimeOfDayKeyValue AS DATETIME)),
                (cast(r.ResultDateKeyValue AS DATETIME) + cast(r.ResultTimeOfDayKeyValue AS DATETIME))) AS ResultDelay
	INTO #LabDelayReport
FROM #MyStat
	     LEFT JOIN deid_uf.LabComponentResultFact r ON #MyStat.LabComponentKey = r.LabComponentKey
	     LEFT JOIN deid_uf.LabTestComponentResultMappingFact m ON m.LabComponentResultKey = r.LabComponentResultKey
	     LEFT JOIN deid_uf.LabTestFact t ON t.LabTestKey = m.LabTestKey
WHERE OrderPriority = 'STAT'
  AND year(r.OrderedDateKeyValue) > 2012
  AND t.Laboratory IN ('UCSF LAB', 'UCSF MISSION BAY LAB', 'UCSF BLOOD GAS LAB', 'UCSF MOUNT ZION CLINICAL LABS')


SELECT * from #LabDelayReport

-- Lets generate a report looking at the mean delay by lab
SELECT ComponentName,
       Laboratory,
       AVG(ResultDelay) AS MeanDelay
FROM #LabDelayReport
GROUP BY ComponentName, Laboratory
ORDER BY ComponentName, AVG(ResultDelay)


/* Lets tie labs back to a patient and their encounter
First I want to find all admissions where the primary diagnosis was prostate cancer
Note: this is an example only. Because I'm not linking to the DiagnosisBridgeTable, I'm only finding cases where
the primary diagnosis was prostate cancer, and likely missing those where prostate cancer was an additional diagnosis.
Also, for simplicity, I'm limiting my query to post-2012 (after EPIC implementation)
*/


SELECT count(*)
FROM deid_uf.EncounterFact e
	     LEFT JOIN deid_uf.DiagnosisTerminologyDim d ON d.DiagnosisKey = e.PrimaryDiagnosisKey
WHERE d.Type = 'ICD-10-CM'
  AND d.Value = 'C61'
  AND e.IsHospitalAdmission = 1
  AND e.PatientDurableKey != '-1'
  AND year(e.AdmissionDateKeyValue) > 2012


SELECT e.EncounterKey,
       e.PatientDurableKey,
       e.AdmissionDateKeyValue,
       e.DischargeDateKeyValue
FROM deid_uf.EncounterFact e
	     LEFT JOIN deid_uf.DiagnosisTerminologyDim d ON d.DiagnosisKey = e.PrimaryDiagnosisKey
WHERE d.Type = 'ICD-10-CM'
  AND d.Value = 'C61'
  AND e.IsHospitalAdmission = 1
  AND e.PatientDurableKey != '-1'
  AND year(e.AdmissionDateKeyValue) > 2012


SELECT count(*)
	--        e.PatientDurableKey,
	--        e.AdmissionDateKeyValue,
	--        e.DischargeDateKeyValue,
	--        r.CollectionDateKeyValue,
	--        r.ResultDateKeyValue
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 d.Type = 'ICD-10-CM'
  AND d.Value = 'C61'
  AND e.IsHospitalAdmission = 1
  AND e.PatientDurableKey != '-1'
  AND year(e.AdmissionDateKeyValue) > 2012

SELECT count(*)
	--        e.PatientDurableKey,
	--        e.AdmissionDateKeyValue,
	--        e.DischargeDateKeyValue,
	--        r.CollectionDateKeyValue,
	--        r.ResultDateKeyValue
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 d.Type = 'ICD-10-CM'
  AND d.Value = 'C61'
  AND e.IsHospitalAdmission = 1
  AND e.PatientDurableKey != '-1'
  AND year(e.AdmissionDateKeyValue) > 2012
  AND r.CollectionDateKeyValue >= e.AdmissionDateKeyValue

SELECT count(*)
	--        e.PatientDurableKey,
	--        e.AdmissionDateKeyValue,
	--        e.DischargeDateKeyValue,
	--        r.CollectionDateKeyValue,
	--        r.ResultDateKeyValue
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 d.Type = 'ICD-10-CM'
  AND d.Value = 'C61'
  AND e.IsHospitalAdmission = 1
  AND e.PatientDurableKey != '-1'
  AND year(e.AdmissionDateKeyValue) > 2012
  AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)


SELECT count(*)
	--        e.PatientDurableKey,
	--        e.AdmissionDateKeyValue,
	--        e.DischargeDateKeyValue,
	--        r.CollectionDateKeyValue,
	--        r.ResultDateKeyValue
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
	     INNER JOIN #MyStat ON #MyStat.LabComponentKey = r.LabComponentKey
WHERE d.Type = 'ICD-10-CM'
  AND d.Value = 'C61'
  AND e.IsHospitalAdmission = 1
  AND e.PatientDurableKey != '-1'
  AND year(e.AdmissionDateKeyValue) > 2012
  AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)

SELECT e.PatientDurableKey,
       e.AdmissionDateKeyValue,
       e.DischargeDateKeyValue,
       r.CollectionDateKeyValue,
       r.ResultDateKeyValue,
       r.ProcedureName,
       r.ComponentName,
       r.Value,
       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
	     INNER JOIN #MyStat ON #MyStat.LabComponentKey = r.LabComponentKey
WHERE d.Type = 'ICD-10-CM'
  AND d.Value = 'C61'
  AND e.IsHospitalAdmission = 1
  AND e.PatientDurableKey != '-1'
  AND year(e.AdmissionDateKeyValue) > 2012
  AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)