Obtain Most Recent Pymt Date and Corresponding Pymt Amt

[SpeedThink]

Currently, I am using the following query with Brio Decision Support on an Oracle Database:

SELECT DISTINCT AL2.CONTRACT_ID, AL3.ENCOUNTER_NO,
trunc(AL3.ADMIT_DATE), AL3.DATE_BILLED, AL3.TOTAL_CHARGES,
AL3.EXPECTED_REIMBURSEMENT, AL2.TOTAL_PAYMENTS,
AL3.EXPECTED_REIMBURSEMENT - AL2.TOTAL_PAYMENTS BALANCE,
AL3.EXPECTED_REIMBURSEMENT - AL3.TOTAL_PAYMENTS BALANCE_ALL_PARTIES,
AL3.TOTAL_PAYMENTS,
AL2.TOTAL_PAYMENTS / AL3.EXPECTED_REIMBURSEMENT RATIO, AL1.PAYMENT_AMOUNT
FROM HC_bcd.ENCOUNTER_PAYMENT_DETAIL AL1, HC_bcd.ENCOUNTER_PAYOR AL2, HC_bcd.PATIENT_ENCOUNTER AL3 WHERE (AL2.ENCOUNTER_NO=AL3.ENCOUNTER_NO AND AL1.ENCOUNTER_NO=AL3.ENCOUNTER_NO) AND (AL2.RANK=1 AND
AL2.CONTRACT_ID Not In ('NCT', 'GVN', 'GVA')
AND AL1.PAYMENT_AMOUNT IN (SELECT MAX ( AL6.PAYMENT_DATE )
FROM HC_bcd.ENCOUNTER_PAYMENT_DETAIL AL6) AND
AL1.TRANSACTION_CODE in ('47003','47008','47009') AND
AL3.EXPECTED_REIMBURSEMENT>0 AND
AL3.EXPECTED_REIMBURSEMENT - AL3.TOTAL_PAYMENTS>0 AND
AL2.TOTAL_PAYMENTS / AL3.EXPECTED_REIMBURSEMENT<=0.9 AND
AL3.ENCOUNTER_NO IN (SELECT AL8.ENCOUNTER_NO
FROM HC_bcd.ENCOUNTER_PAYMENT_DETAIL AL6,
HC_bcd.PATIENT_ENCOUNTER AL8
WHERE (AL6.ENCOUNTER_NO=AL8.ENCOUNTER_NO) AND
(trunc(AL6.DATE_UPDATED) = trunc(sysdate) - 23)))
ORDER BY 1, 2, 8


I am interested in obtaining all encounters that had a payor payment interface the Oracle Database Application 23 days ago, the payor payments to date, the date of the most recent payor payment and the corresponding payment amount, etc.

Noted, usually there are multiple payor payments on the same day. If I use "sum(payment_amount)" in my query, I am able to aggregate the payment amount on 1 record. However, when I try to use the "sum(payment_amount)" and "Max(Payment_date)" together, I am not able to obtain the desired result. Instead, I receive the latest Payment_Date but the figure in the Payment_Amount column is a sum of all payments received for several dates not just the latest payment date.

Also, I continually receive an Oracle error related to inconsistent data type when I try to limit the payment amount by the latest payment date as displayed in the sql syntax above.

Any clue as to a possible resolution?

Thanks in advance.

 

[TonyGroves]

You're in the wrong forum - this is for MySQL!

Try formatting your code to make it readable and post in the Oracle forum instead.


-----
ALTER world DROP injustice, ADD peace;