Lab Orders

select
 lot.lab_order_id
 ,lo.PATIENT_ID
 , pt.first_name as "PATIENT_FIRST_NAME"
 , pt.last_name as "PATIENT_LAST_NAME"
 , pt.dob as "PATIENT_DOB"
 , lo.resolving_document_id
 , lo.visit_note_id
 , lo.non_visit_note_id
 , lo.req_number
 , lo.ordering_provider as "ORDERING_PROVIDER_ID"
 , concat(o_user.first_name, ' ', o_user.last_name) as "ORDERING_PROVIDER"
 , lo.lab_vendor
 , lo.lab_site
 , lo.date_for_test
 , lo.follow_up_notes
 , lo.order_status
 , lo.is_eorder
 , lo.pt_cond
 , lo.stat
 , lo.bill_type
 , lo.testcenternotes
 , lo.frequency
 , lo.frequency_end_date
 , lo.has_reminder
 , lo.remind_date
 , lot.test_id
 , lot.code as "TEST_ORDER_CODE"
 , lot.name
 , concat('(', lo.signed_by_user_id, ')', ' ', s_user.first_name, ' ', s_user.last_name, ', ', s_user.credentials) as "SIGNED_BY"
 , lo.signed_time
 , concat('(', lo.created_by_user_id, ')', ' ', c_user.first_name, ' ', c_user.last_name, ', ', c_user.credentials) as "CREATED_BY"
 , lo.creation_time
 , concat('(', lo.deleted_by_user_id, ')', ' ', d_user.first_name, ' ', d_user.last_name, ', ', d_user.credentials) as "DELETED_BY"
 , lo.DELETION_TIME
 , lo.last_modified
from lab_order lo
  left join lab_order_tests lot on lot.lab_order_id = lo.id and lot.is_deleted = false
  join patient pt on pt.id = lo.patient_id
  left join user c_user on lo.created_by_user_id = c_user.id
  left join user o_user on lo.ordering_provider = o_user.id  
  left join user s_user on lo.signed_by_user_id = s_user.id
  left join user d_user on lo.deleted_by_user_id = d_user.id
group by all;