Appointments

with appt_status as (
    select appointment_id,
            status,
            note,
            creation_time
    from appointment_status
    qualify row_number() over (partition by appointment_id order by creation_time desc) = 1

)
select
    p.id as PATIENT_ID
  , concat(p.first_name, ' ', p.last_name) as PATIENT_NAME
  , p.dob
  , appt_time
  , concat('(',u.id, ') ',u.first_name, ' ', u.last_name) as PROVIDER_ON_APPT
  , concat('(',a.created_by_user_id, ') ',ppu_user.first_name, ' ', ppu_user.last_name) as PRIMARY_CARE_PROVIDER
  , a.appt_type
  , sl.name as SERVICE_LOCATION_NAME
  , stat.status
  , a.description
  , a.copay_amount
  , stat.note
  , concat('(',a.created_by_user_id, ') ',c_user.first_name, ' ', c_user.last_name) as CREATED_BY
  , a.creation_time
  , concat('(',a.deleted_by_user_id, ') ',d_user.first_name, ' ', d_user.last_name) as DELETED_BY
  , a.deletion_time
  , a.last_modified
from appointment a
  left join service_location sl on sl.id = a.service_location_id
  left join appt_status stat on stat.appointment_id = a.id
  join patient p on p.id = a.patient_id
  join user u on u.id = a.physician_user_id
  left join user c_user on c_user.id = a.created_by_user_id
  left join user d_user on d_user.id = a.deleted_by_user_id
  join user ppu_user on ppu_user.id = p.primary_physician_user_id;