Unanswered: SQL to obtain sum of prior payments for time frame
I created a SQL script to monitor payment variances on patient accounts as follows:
Select ep.contract_id Cont, pe.account_no Acct No,
sum(epd.payment_amount) Pymts, ep.total_payments InsPymts, pe.admit_date AdmDate, pe.date_updated BillDate, pe.total_charges,
nvl(pe.total_payments,0) - nvl(ep.total_payments OtherPymt) OtherPymt, pe.expected_reimbursement
from patient_encounter pe, encounter_payor ep,
where pe.account_no = ep.account_no
and pe.account_no = epd.account_no
and epd.transaction_code in ('4700384', '4700813') Ins Payor Codes!
and trunc(epd.date_updated) = trunc (sysdate)
The problem is that this query only picks up accounts that have a Insurance payment today! Therefore, when management begins to review the variances, additional payments might come in the next day or next week and therefore the account no longer have a variance between the expected amount and the sum of the Insurance Payments!!
What changes do I need to make to the SQL script that will allow me to obtain Insurance payment amounts for a particular range of dates prior to today? For example,
Current Date Need Sum of Payments for
Sept 30 Sept 1 - Sept 30
Oct 14 Sept 1 through Oct 14
Oct 31 Sept 1 through Oct 31
Note, There is no payment date for the Insurance Payments in the tables. Also, as you can see, the transaction codes triggers today's Ins Payment to be populated in the results. It appears that the encounter payment detail is updated everytime an Insurance Payment is received!!