SQL Logic: Revflow_EOB_to_Billing_Charge_Connect

One of the most common joins is EOB/835 tables to charge records. This following join shows how to use the SVW_BILLING_CHARGE table. You can use the SVW_REVFLOW_D_CHARGE if you'd prefer, but some modifications would need to be made to the join statement below.

select li.ACCTNO 
     , li.EOB_KEY
     , li.CPT
     , li.DOS
     , li.CLAIM_DATE
     , li.CLINICCODE
     , ch.PATIENT_ACCOUNT_NUMBER
     , ch.CPT_CODE
     , ch.DATE_OF_SERVICE
     , ch.CHARGE_DATE
     , ch.GPMS_CODE
from public.SVW_REVFLOW_EOB_RELEASE_LINE_ITEM li
left join (select bc.PATIENT_ACCOUNT_NUMBER, p.LAST_NAME, p.FIRST_NAME, bc.CPT_CODE, bc.DATE_OF_SERVICE, bc.CHARGE_DATE, c.GPMS_CODE
                from PUBLIC.SVW_BILLING_CHARGES bc
                join PUBLIC.SVW_PATIENT p on bc.PATIENT_ACCOUNT_NUMBER = p.PATIENT_ACCOUNT_NUMBER
                join PUBLIC.SVW_CLINICS c on bc.PLACE_OF_SERVICE_ID = c.PLACE_OF_SERVICE_ID) ch
    on trim(li.CPT) = trim(ch.CPT_CODE) 
    and li.dos::date = ch.DATE_OF_SERVICE::date 
    and trim(ch.GPMS_CODE) = trim(li.CLINICCODE) 
    and cast(ch.PATIENT_ACCOUNT_NUMBER as varchar) = right(trim(li.ACCTNO),length(trim(li.ACCTNO))-3)