Referrals

with cte_dx as (
    select
    id,
    code,
    description
    from icd10
    qualify row_number() over (partition by id order by code asc) = 1
)
select
    ro.id as "REFFERAL_ORDER_ID"
  , ro.patient_id
  , pt.first_name as "PATIENT_FIRST_NAME"
  , pt.last_name as "PATIENT_LAST_NAME"
  , pt.dob as "PATIENT_DOB"
  , ro.chart_feed_date
  , ro.resolution_state
  , ro.visit_note_id
  , ro.non_visit_note_id
  , concat(s_user.first_name, ' ', s_user.last_name) as "FROM_USER"
  , ro.recipient_contacttype
  , ro.recipient_org_name as "RECIPIENT_ORG_NAME"
  , concat(ro.recipient_firstname, ' ', ro.recipient_lastname) as "RECIPIENT_PHYSICIAN_NAME"
  , ro.recipient_specialty
  , ro.recipient_npi
  , ro.recipient_credentials
  , ro.delivery_method
  , ro.recipient_fax
  , ro.fax_status
  , ro.email_to
  , ro.clinical_reason
  , ro.authorization_for
  , ro.auth_number
  , icd10code_id
  , dx.code
  , dx.description
  , ro.delivery_date as "POST_DATED_DELIVERY_DATE"
  , ro.processing_status
  , concat('(', ro.signed_by_user_id, ')', ' ', s_user.first_name, ' ', s_user.last_name, ', ', s_user.credentials) as "SIGNED_BY"
  , ro.SIGNED_TIME as "SIGNED_TIME"
  , concat('(', ro.prescribing_user_id, ')', ' ', p_user.first_name, ' ', p_user.last_name, ', ', p_user.credentials) as "PRESCRIBING_USER"
  , concat('(', ro.created_by_user_id, ')', ' ', c_user.first_name, ' ', c_user.last_name, ', ', c_user.credentials) as "CREATED_BY"
  , ro.creation_time 
  , concat('(', ro.deleted_by_user_id, ')', ' ', d_user.first_name, ' ', d_user.last_name, ', ', d_user.credentials) as "DELETED_BY"
  , ro.deletion_time
  , ro.last_modified
from referral_order ro
  join patient pt on pt.id = ro.patient_id
  left join referral_order_dx rodx on rodx.referralorder_id = ro.id
  left join cte_dx dx on dx.id = rodx.icd10code_id
  left join user p_user on p_user.id = ro.prescribing_user_id
  left join user c_user on ro.created_by_user_id = c_user.id
  left join user s_user on ro.signed_by_user_id = s_user.id
  left join user d_user on ro.deleted_by_user_id = d_user.id
where send_to_name != ' '
and rodx.is_deleted = FALSE;