select
r.patient_id
, pt.first_name as "PATIENT_FIRST_NAME"
, pt.last_name as "PATIENT_LAST_NAME"
, pt.dob as "PATIENT_DOB"
, lr.lab_report_id
, lr.collected_date
, r.chart_feed_date
, r.requisition_number
, r.ordering_provider_name
, r.vendor_name
, r.order_status
, lr.resulted_date
, lr.accession_status
, lr.accession_number
, lr.test_category
, lr.test_name
, lr.value
, lr.units
, lr.value_type
, lr.reference_min
, lr.reference_max
, lr.is_abnormal
, lr.abnormal_flag
, lr.report_text
, lr.value_note
, lr.note
, lr.loinc
, lr.is_deleted
, concat('(', r.signed_by_user_id, ')', ' ', s_user.first_name, ' ', s_user.last_name, ', ', s_user.credentials) as "SIGNED_BY"
, r.signed_time
, concat('(', r.created_by_user_id, ')', ' ', c_user.first_name, ' ', c_user.last_name, ', ', c_user.credentials) as "CREATED_BY"
, r.creation_time
, concat('(', r.deleted_by_user_id, ')', ' ', d_user.first_name, ' ', d_user.last_name, ', ', d_user.credentials) as "DELETED_BY"
, r.DELETION_TIME
, r.last_modified
from report r
left join lab_result lr on lr.lab_report_id = r.id
join patient pt on pt.id = r.patient_id
left join user s_user on r.signed_by_user_id = s_user.id
left join user c_user on r.created_by_user_id = c_user.id
left join user d_user on r.deleted_by_user_id = d_user.id
where report_type = 'Lab'
and is_deleted = 'FALSE';
