/*******************************************************************************
GPC ticket 114
*******************************************************************************/

/* PATIENTS
Ethnicity_CD isn't part of the patient dimension
*/
select count(*) from (
  Select Patient_num, Birth_date, Sex_CD, /*Ethnicity_CD,*/ Race_CD
  From Blueherondata.Patient_dimension
  ); --2035435

/* ENCOUNTERS (visits): CORE ELEMENTS
- I assumed that Facility_location is represented by the location_cd? Once 
  linked to the code_lookup table:
  See https://www.i2b2.org/software/files/PDF/current/CRC_Design.pdf

- What does EncounterType_CD represent?
*/
select count(*) from (
  Select distinct O.Patient_num, O.Encounter_num, E.Start_date, E.End_date, 
                  O.Provider_ID, E.location_cd/*, E.EncounterType_CD, E.Facility_CD */
  From blueherondata.Observation_fact O
  Join blueherondata.visit_dimension E on O.Encounter_num = E.Encounter_num
  );
  
/* INPATIENT STAY OBSERVATIONS
Issue: Are we agreed to add observation facts for Encounter data below or expand 
Visit_dimension?

- Same facility/location comment and EncounterType_CD
- We don't have any LOINC concepts
*/
Select Count(*) from (
  select distinct 
    O.Patient_num, O.Encounter_num, E.Start_date, E.End_date, 
    O.Provider_ID, E.location_cd/*E.Facility_location, E.EncounterType_CD, E.Facility_CD*/
  From blueherondata.Observation_fact O
  Join blueherondata.visit_dimension E On O.Encounter_num = E.Encounter_num
  Where O.Concept_CD = 'LOINC:46579-9'
  );
select * from blueherondata.concept_dimension where upper(concept_cd) like 'LOINC%'; --0 results

/* Additional Observation facts or expanded visit dimension attributes?
> Concept_CD = LOINC:46579-9 Discharge disposition
> 52523-8 Discharge status
> 38999-9 Discharge DRG (modifier for DRG type?)
> 57830-2 Admission request
Hmm...
*/
/* DIAGNOSES: PRINCIPAL DISCHARGE DIAGNOSES
I think PROVIDER_ID = observer_cd as per
https://www.i2b2.org/software/files/PDF/current/CRC_Design.pdf
*/
Select count(*) from (
  select 
    O.Patient_num, O.Encounter_num/*, E.EncounterType_CD*/, E.Start_date, 
    /*Observer_CD*/provider_id, Concept_CD, 'Billing diagnosis', '', 'P'
  From blueherondata.Observation_fact O
  Join blueherondata.visit_dimension E On O.Encounter_num = E.Encounter_num 
  Where O.Modifier_CD = 'BillingDiagnosis?\DischargePrincipal?'
  );
select modifier_path, name_char, modifier_cd from blueherondata.modifier_dimension 
where lower(modifier_path) like '%billing%' or
lower(modifier_path) like '%discharge%';
/* ...results in:
\Diagnosis\Billing\	                  Billing (IDX, UHC)                    (null)
\Diagnosis\Billing\UHC_PROCEDURE\	    Hospital procedure (UHC)              DiagObs:UHC_PROCEDURE
\Diagnosis\Billing\UHC_DIAGNOSIS\	    Hospital diagnosis (UHC)              DiagObs:UHC_DIAGNOSIS
\Diagnosis\Billing\UKP\	              Clinic (IDX)                          DiagObs:Clinic
\Diagnosis\Billing\Primary\	           Primary billing diagnosis (IDX, UHC) DiagObs:Primary
*/


/* VITALS: WEIGHT
Again, we don't have lonc, but...
*/
select count(*), concept_cd
from blueherondata.concept_dimension where lower(name_char) like '%weight%'
group by concept_cd
order by count(*) desc;
-- Most common one: 104	KUH|FLO_MEAS_ID:14

Select Count(*) from (
  select Patient_num, Encounter_num, Start_date, Nval_num
  From blueherondata.Observation_fact
  Where Concept_CD = 'LOINC:3141-9'
  );

/* HEIGHT
*/
select count(*), concept_cd
from blueherondata.concept_dimension where lower(name_char) like '%height%'
group by concept_cd
order by count(*) desc;
-- Most common one: 86	KUH|FLO_MEAS_ID:11

Select Count(*) from (
  select Patient_num, Encounter_num, Start_date, Nval_num
  From blueherondata.Observation_fact
  Where Concept_CD = 'LOINC:3137-7'
  );


/* SYSTOLIC BP
*/
select count(*), concept_cd
from blueherondata.concept_dimension where lower(name_char) like '%systolic%'
group by concept_cd
order by count(*) desc;
-- Most common one: 140	KUH|FLO_MEAS_ID:5_SYSTOLIC

Select Count(*) from (
  select Patient_num, Encounter_num, Start_date, Nval_num
  From blueherondata.Observation_fact
  Where Concept_CD = 'LOINC:8480-6'
  );


/* DIASTOLIC BP
*/
select count(*), concept_cd
from blueherondata.concept_dimension where lower(name_char) like '%diastolic%'
group by concept_cd
order by count(*) desc;
-- Most common one: 140	KUH|FLO_MEAS_ID:5_DIASTOLIC

Select Count(*) from (
  select Patient_num, Encounter_num, Start_date, Nval_num
  From blueherondata.Observation_fact
  Where Concept_CD = 'LOINC:8462-4'
  );

/* PROCEDURES
*/
Select count(*) from (
  select O.Patient_num, O.Encounter_num, /*E.EncounterType_CD,*/ E.Start_date, O.Provider_id, O.Concept_CD, O.Concept_CD
  From blueherondata.Observation_fact O
  Join blueherondata.visit_dimension E
  On O.Encounter_num = E.Encounter_num
  Where O.Concept_CD LIKE '%CPT:%'
  );
