SQL Script Modification - Sysdate Manipulation

[SpeedThink]

Currently, my sql statements causes a encounter_no to populate my variance report when there is a Insurance Payor payment on today. When a encounter no shows on the variance report, the Revenue Collections area start to research the Variance to determine the reason for underpayment. However, if an additional payment arrives for the same encounter no, for example - 7 days after the first payment is received, there might not be a variance anymore! Therefore, all of the effort spent researching the "Variance" is "wasted."

Issue - It appears that the Variance Report should be on a time lag, if you will, for approximately 2 weeks.

Example - Exhibit Date on
Enc No. Pymt Amt Total Ins Pymts Underpay Amt Var Rpt
378201 $1000 $1000 $12000 9/17/03
378201 $12000 $13000 $0 9/24/03

My initial thought is to modify the statement within my sql script from "and trunc(epd.date_updated) = trunc(sysdate)" to "and trunc(epd.date_updated) between trunc(sysdate)-14 and trunc(sysdate)."

Below is the sql statement that should be modified:

SELECT pe.encounter_no Enc No, sum(epd.payment_amount) Payment, pe.admit_date Admit, pe.total_charges Charges, pe.expected reimbursement Exp Reimb,
nvl(ep.total_payments,0) TotInsPymts,
pe.total_payments TotPymts,
from patient_encounter pe, encounter_payor ep,
encounter_payment_detail epd
where pe.encounter_no = ep.encounter_no
and trunc(epd.date_updated) = trunc(sysdate)
and pe.expected_reimbursement > 0
and (nvl(pe.expected_reimbursement,0) - (nvl(pe.total_payments,0))) > 0
and pe.encounter_no = epd.encounter_no
and epd.payment_amount >0

Thanks in advance!

 

[r937]

your date range test looks okay

you'll probably want to investigate whether to SUM() other fields besides just payment_amount, and you definitely will want to use a GROUP BY clause

rudy

http://r937.com/

 

[SpeedThink]

r937,

I did not initially include the entire SQL script thinking that i just need to modify the sysdate portion of the SQL Script to resolve the "problem."

The entire SQL script is as follows:

SELECT ep.contract_id CONT,
pe.encounter_no ENC,
sum(epd.payment_amount) PYMNT,
pe.admit_date DDATE, pe.date_updated BDATE,
pe.total_charges CHGS,
pe.expected_reimbursement EXP,
nvl(ep.total_payments,0) EPPMT,
nvl(pe.total_payments,0) - nvl(ep.total_payments,0) OTHPMT,
pe.total_payments TOTPMT,
(nvl(pe.expected_reimbursement,0) - (nvl(ep.total_payments,0))) BAL
FROM patient_encounter pe, encounter_payor ep,
encounter_payment_detail epd
WHERE pe.encounter_no = ep.encounter_No
and ep.rank = 1
ANDEP.CONTRACT_ID NOT IN ('NCT','GVN','GVA')
and epd.transaction_code in ('4700384','4700813','4700812','4700564', '4700301')
AND trunc(epd.date_updated) = trunc(sysdate)
and pe.expected_reimbursement > 0
and pe.encounter_no = epd.encounter_No
and epd.payment_amount > 0
groupby ep.contract_id, pe.encounter_no,
pe.admit_date, pe.date_updated, epd.payment_date,
pe.total_charges, pe.expected_reimbursement,
nvl(ep.total_payments,0),nvl(pe.total_payments,0)-nvl(ep.total_payments,0),
pe.total_payments,
(nvl(pe.expected_reimbursement,0) - (nvl(pe.total_payments,0)))
ORDERBY CONT, BAL desc

SpeedThink

 

[r937]

you definitely will want to use a GROUP BY clause

 

[SpeedThink]

A group by clause is stated, See the 7th line from the bottom of the SQL Script. Please clarify.

 

[carp]

I see "groupby" but no "group by". Is this a typo in your code or in your posting?

 

[Golom]

... and "orderby" should probably be "ORDER BY" ... unless your DBMS accepts "groupby" and "orderby" as acceptable contractions of the standard SQL.

 

[SpeedThink]

The query runs! The "groupby" and "orderby" are actually "group by" and "order by", respectively. What you see is just the result of my using copy/paste from Microsoft Word to the "Step 1 Message" area!

The SQL Script needs to be modified so that the users can see any additional payments for a particular encounter that occurred during a 2 week timeframe prior to the current date(A 2-week lag, if you will). At the same time, I am interested in still using a Insurance Payment as the "trigger" - in essence, if there is a insurance payment, show the encounter number on the report. Otherwise, the encounter number should not show!

 

[carp]

What is the difference between sum(epd.payment_amount) and the total_payment columns (I see you have one in ep and one in pe - what is the difference between these two?) If a given encounter can have multiple rows in these tables and the values of date_updated, total_payment, or expected_reimbursement will change across these rows, then these columns should be left out or replaced with aggregate functions such as max(date_updated).

As for getting values across a window of time,

WHERE epd.date_updated >= trunc(sysdate)-14

should do the trick without disabling any indexes on your date_updated column.

 

[SpeedThink]

carp,

great catch!! on the Total_Payments field in the Patient Encounter and Encounter Payor tables. That has been an issue of mine since I started working with this Oracle Database. Here is the issue, Total_Payments on the Encounter Payor Table is only the a running total of payments from the Insurance Payor! Total_Payments on the Patient Encounter Table is a running total of all payments regardless of source (Insurance Payments, Patient Payments, etc.).

Further, Sum(epd.payment_amount) is summing all individual payment amounts for a particular day. The column on the Variance Report titled "Total Pymts" is a cumulative total of Insurance Payments and Patient Payments at any given point in time.

Therefore, shouldn't the second iteration of the modification of my SQL script contain the following line: "And trunc(epd.date_updated) is between trunc(sysdate-14) and trunc(sysdate)" ???

 

[SpeedThink]

Has anyone encountered a challenge such as this?

It appears that the Variance Report should be on a 2 to 3 week lag whereby the "epd.date_updated" field in the "where" section should be "...between trunc(sysdate) - 25 and trunc(sysdate) - 14."

For those encounters identified that had a payment during this time frame, I would like to see all Insurance Payments and the payment dates for those payments from within the timeframe to current date.

It appears that this is combining scalar functions (detail such as payment amount) and aggregate functions such as sum,max,etc.

The last column should be a ratio of Total Insurance Payments/Expected Reimbursement (ep.total_payments/pe.expected reimbursement). Therefore, as additional payments from the Insurance Payor are received, this ratio should decrease. The justification for the modification in the SQL Script is to prevent "wasted effort" on the front-end such as pulling Explanation of Benefit forms, and researching a "variance" that will no longer exist 14 days or so after the first payment is received from the Insurance Payor.

 

[Rachoor]

May be you need to use sub query for this kind of problem.