Patient History

select
    ph.id as "PATIENT_HISTORY_ID"
  , ph.patient_id
  , pt.first_name as "PATIENT_FIRST_NAME"
  , pt.last_name as "PATIENT_LAST_NAME"
  , pt.dob as "PATIENT_DOB"
  , ph.value as "NOTE"
  , ph.rank as "DISPLAY_ORDER"
  , ph.creation_time
  , concat(c_user.first_name, ' ', c_user.last_name) as "CREATED_BY"
from patient_history ph
  join patient pt on pt.id = ph.patient_id
  left join user c_user on c_user.id = ph.created_by_user_id
where ph.type = 'Maintenance'
  and ph.deletion_time is null
order by ph.patient_id, ph.rank;

select
    ph.patient_id
  , pt.first_name as "PATIENT_FIRST_NAME"
  , pt.last_name as "PATIENT_LAST_NAME"
  , pt.dob as "PATIENT_DOB"
  , ph.relationship_type
  , ph.value as "CONDITION"
  , ph.icd9_id
  , ph.snomed_id
  , ph.creation_time
from patient_history ph
  join patient pt on pt.id = ph.patient_id
where ph.type = 'Family'
  and ph.relationship_type is not null
  and ph.deletion_time is null
order by ph.patient_id, ph.rank;

select
    ph.type
  , count(*) as "ENTRIES"
  , count(distinct ph.patient_id) as "PATIENTS"
from patient_history ph
where ph.deletion_time is null
group by ph.type
order by "ENTRIES" desc;