select
vn.patient_id
, pt.first_name as "PATIENT_FIRST_NAME"
, pt.last_name as "PATIENT_LAST_NAME"
, pt.dob as "PATIENT_DOB"
, vn.id as "VISIT_NOTE_ID"
, chart_feed_date as "VISIT_NOTE_DATE"
, sl.name as "SERVICE_LOCATION"
, bi.id as "BILL_ITEM_ID"
, billing_date
, billing_status
, b.billing_status_detail
, billing_error
, ref_number
, b.notes
, cpt
, modifier_1
, modifier_2
, modifier_3
, modifier_4
, LISTAGG(icd10_code,', ') WITHIN GROUP (ORDER BY dx.seqno) AS "ICD_10 CODES"
, concat(floor(vn.time_with_patient/60/60%24), ':', floor(vn.time_with_patient/60%60)) as "TIME_WITH_PATIENT"
, concat('(', vn.signed_by_user_id, ')', ' ', s_user.first_name, ' ', s_user.last_name, ', ', s_user.credentials) as "VISIT_NOTE_SIGNED_BY"
, vn.signed_time as "VISIT_NOTE_SIGNED_TIME"
, concat('(', bi.created_by_user_id, ')', ' ', c_user.first_name, ' ', c_user.last_name, ', ', c_user.credentials) as "BILL_CREATED_BY"
, bi.creation_time as "BILL_CREATION_TIME"
, bi.last_modified as "BILL_LAST_MODIFIED"
, concat(ru.first_name, ' ', ru.last_name, ', ', ru.credentials) as "RENDERING_PROVIDER"
, concat(su.first_name, ' ', su.last_name, ', ', su.credentials) as "SUPERVISING_PROVIDER"
, concat(bu.first_name, ' ', bu.last_name, ', ', bu.credentials) as "BILLING_PROVIDER"
from visit_note vn
left join bill b on b.visit_note_id = vn.id
join patient pt on pt.id = vn.patient_id
left join bill_item bi on bi.bill_id = b.id
left join bill_item_dx dx on dx.bill_item_id = bi.id
left join service_location sl on sl.id = b.service_location_id
left join user c_user on bi.created_by_user_id = c_user.id
left join user s_user on vn.signed_by_user_id = s_user.id
left join user ru on ru.physician_id = b.rendering_provider_id
left join user su on su.physician_id = b.supervising_provider_id
left join user bu on bu.physician_id = b.billing_provider_id
where vn.deleted_by_user_id is null
and bi.is_deleted = false
and dx.is_deleted = false
group by vn.patient_id, pt.first_name, pt.last_name, pt.dob, vn.id, chart_feed_date, bi.id, billing_date, billing_status, b.billing_status_detail, billing_error, ref_number, b.notes, cpt, modifier_1, modifier_2, modifier_3, modifier_4, vn.time_with_patient, vn.signed_by_user_id, vn.signed_time, bi.created_by_user_id, bi.creation_time, bi.last_modified, s_user.first_name, s_user.last_name, s_user.credentials, c_user.first_name, c_user.last_name, c_user.credentials, dx.bill_item_id, sl.name, bi.seqno, ru.first_name, ru.last_name, ru.credentials, su.first_name, su.last_name, su.credentials, bu.first_name, bu.last_name, bu.credentials
order by vn.patient_id, chart_feed_date, vn.id, bi.last_modified, bi.seqno;
