Health Maintenance

select
    hm.id as "HEALTH_MAINTENANCE_ID"
  , hm.patient_id
  , pt.first_name as "PATIENT_FIRST_NAME"
  , pt.last_name as "PATIENT_LAST_NAME"
  , pt.dob as "PATIENT_DOB"
  , hm.name as "MEASURE_NAME"
  , hm.measure_code
  , hm.date as "COMPLETION_DATE"
  , hm.note
  , hm.normal_result
  , hm.abnormal_result
  , concat(hm.firstname, ' ', hm.lastname) as "PHYSICIAN_NAME"
  , hm.confidential
  , hm.created_date
from health_maintenance hm
  left join patient pt on pt.id = hm.patient_id
where hm.deleted_date is null
order by hm.date desc;

select
    hm.measure_code
  , hm.name as "MEASURE_NAME"
  , count(distinct hm.patient_id) as "PATIENTS_WITH_COMPLETION"
  , min(hm.date) as "EARLIEST_COMPLETION"
  , max(hm.date) as "MOST_RECENT_COMPLETION"
from health_maintenance hm
where hm.deleted_date is null
  and hm.measure_code is not null
group by hm.measure_code, hm.name
order by hm.name;