SQL - Scalar and Aggregate data on same record

[SpeedThink]

Currently, I am using the following query to extract data from an Oracle database via a Hyperion (Brio) query:

SELECT AL2.ACCT, AL3.ENCOUNTER_NO, trunc(AL3.ADMIT_DATE),
trunc(AL3.DISCHARGE_DATE), AL3.DATE_BILLED, AL3.TOTAL_CHARGES, AL3.EXPECTED_REIMBURSEMENT, AL2.TOTAL_PAYMENTS,
AL3.EXPECTED_REIMBURSEMENT - AL2.TOTAL_PAYMENTS,
AL3.EXPECTED_REIMBURSEMENT - AL3.TOTAL_PAYMENTS, AL2.TOTAL_PAYMENTS /AL3.EXPECTED_REIMBURSEMENT, AL3.TOTAL_PAYMENTS - AL2.TOTAL_PAYMENTS,
AL3.TOTAL_CHARGES - Sum (AL2.NONCOVERED_PT_CHARGES +
AL2.NONCOVERED_WO_CHARGES), AL3.TOTAL_CHARGES - Sum
(AL6.ADJUSTMENT_AMOUNT), AL1.PAYMENT_AMOUNT, AL1.PAYMENT_DATE,
AL1.BATCH_NO FROM ENCOUNTER_DETAIL AL1,
ENCOUNTER_PAYOR AL2, PATIENT_ENCOUNTER AL3,
ENCOUNTER_TRANSACTION AL6 WHERE ( AL1.ENCOUNTER_NO =
AL3.ENCOUNTER_NO (+) AND AL2.ENCOUNTER_NO=AL3.ENCOUNTER_NO AND AL6.ENCOUNTER_NO=AL3.ENCOUNTER_NO) AND (AL2.RANK=1 AND
AL2.ACCT Not In ('AC2','AC3','AB4','AB6')
AND AL1.TRANSACTION_CODE in
('2564','2565','2400','2460','2470') AND
AL3.EXPECTED_REIMBURSEMENT(+)>0 AND AL3.EXPECTED_REIMBURSEMENT -
AL3.TOTAL_PAYMENTS>0 AND AL2.TOTAL_PAYMENTS /
AL3.EXPECTED_REIMBURSEMENT<=0.75 AND AL6.TRANSACTION_CODE in ('4003','4009','4010','4011','4015') AND
AL3.DISCHARGE_DATE(+)>='01-01-2004 00:00:00') GROUP BY
AL2.ACCT, AL3.ENCOUNTER_NO, trunc(AL3.ADMIT_DATE),
trunc(AL3.DISCHARGE_DATE), AL3.DATE_BILLED, AL3.TOTAL_CHARGES,
AL3.EXPECTED_REIMBURSEMENT, AL2.TOTAL_PAYMENTS,
AL3.EXPECTED_REIMBURSEMENT - AL2.TOTAL_PAYMENTS,
AL3.EXPECTED_REIMBURSEMENT - AL3.TOTAL_PAYMENTS, AL2.TOTAL_PAYMENTS /
AL3.EXPECTED_REIMBURSEMENT, AL3.TOTAL_PAYMENTS - AL2.TOTAL_PAYMENTS,
AL1.PAYMENT_AMOUNT, AL1.PAYMENT_DATE, AL1.BATCH_NO HAVING
((AL3.TOTAL_CHARGES - Sum (AL6.ADJUSTMENT_AMOUNT)) -
AL3.EXPECTED_REIMBURSEMENT) <> 0 ORDER BY 1, 2, 9


Displayed below is a subset of the results of the SQL Query:

Acct----EncNo----Pymt Amt-------Payment Date
AB1-----136589----162.61---------01/12/05
AB1-----136589----552.58---------01/12/05
AB1-----136589--9,736.66---------01/26/05
AB1-----140856----327.85---------11/24/03
AB1-----140856----584.80---------04/15/04
AB1-----140856-----82.20---------02/01/05
AB2-----136890--1,319.36---------11/29/04
AB2-----136890----513.79---------01/26/05
AB2-----150857--1,829.45---------03/09/04
AB2-----150857--1,389.75---------06/16/04
AB2-----150857----671.45---------07/05/04

As you can see, there are several records for a particular EncNo when there is more than 1 payment! This is not desirable!!

Desired is the SQL Syntax to display all of the data for a particular encounter on just 1 record. For example, "Acct", "EncNo", "date of first payor payment", "amt of first payor payment", "batch no for first payor payment", "date of last payor payment", "amt of last payor payment", and the "batch no for last payor payment" should all be on one record in addition to the data such as "Admit Date," "Date Billed," etc.

Any clue as to how the sql statement should be modified? Would two subqueries be needed? Is this even possible?

I will load the data into Microsoft Access and then plan to run several Append and Update queries on a daily basis to "pull" payment detail from several external Oracle databases. Just curious, what are the advantages/disadvantages with querying data outside of Access and then importing to Access versus initially querying on external tables while within Access (using a link) to initially populate the Access tables?

Thanks in advance!

 

[GingerR]

Do you only want "First Payment" and "Last Payment"? What about if there are more dates than that, such as for #140856?

In any case, you'd have to use some vba (recordsets) to get each related record and put the data into the format you wish. You'd end up with a non-relational table, and Access just isn't built for that (since it's a Relational Database!)

Are you planning on using your results throughout the rest of your database? If so, I'd leave the results as you have them, as this follows Relational Database and Normalization rules!!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

It can be done, but it is more of a question of how.
What version of Oracle?
How much data (rows) will be returned?
Can the SQL pass directly to Oracle or is there some kind of middleware involved?
You should be able to make an OLEDB connection directly to Oracle from the Access program.
Are you familiar with Oracles Analytical Functions? Case Statement?

 

[SpeedThink]

Ginger,

I only want the first payment and the last payment for the customer. In essence, the amount and date of the last payment will be updated with an update query within Access. As you can imagine, on any given day, there will be accounts within the Access Database that will have additional payments and there will be new accounts that will be appended to the database on a daily basis. Therefore, I need to use a append query and then use a update query on a daily basis. The use of VBA would be ideal to automatically perform the append and update queries every day at approximately 5:00 A.M.


Cmmrfrds,

The version of Oracle is v8.1.7. Ideally, I would like to have the Access Database on a shared network and link the 4 to 5 users' workstations to the Access tables. It is a possibility that the number of users will increase to maybe 10.

At the present time, there are approximately 10,000 records to be loaded into the Access Database. In addition, as stated above, there are approximately 20 encounters discharged daily that I need to track a payment variance for.

There is no middleware involved, as far as I know. Therefore, I believe that I am able to make an OLEDB connection directly to Oracle from Access.

I am not familiar with Oracles Analytical Functions. Can you explain how a Case Statement will apply in this instance?

 

[cmmrfrds]

I don't know if the analytical are available until 8.5. Please check. If available I can give you an example.

 

[SpeedThink]

Cmmrfrds,

The analytical is not available on version 8.1.7. We will upgrade to Oracle 9 by May 2005.

Are there other options such as subqueries or would I have to update the info such as "first payment" and "last payment" in more than 1 update query?

One option is to update the payment info within the Access database using Excel spreadsheets instead of automatically via an update query that is run every day.

 

[cmmrfrds]

You could break it down into multiple queries and then run these from a macro.

One way is to break the queries down into separate public functions that can be run from a Macro.

Use an ADO connection to Oracle. For example.

Public Function QueryNum1()

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String

connString = "Provider=MSDAORA.1;" & _
"User ID=yourid;" & _
"Data Source=yourdatabase;" & _
"Password=xxxxxxxxxxx;" & _
"Persist Security Info=True"

cn.ConnectionString = connString
cn.Open connString

sql1 = "select count(*) from yourtable"
rs.Open sql1, cn, adOpenStatic, adLockReadOnly

If not (rs.EOF or rs.BOF) then
'got records' do something
else
'no records'
end if

rs.Close
Set rs = Nothing

End Function

 

[GingerR]

What is a "Last Payment" or "First Payment"? For example, for EncNo = '136589', what is the "first" payment if two payments were made on 1/12/05? Is there some other ID field which maintains some kind of order?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SpeedThink]

cmmrfrds,

I will break down the query and review ADO Connections.

Any insight as to the automation of the query so that each day at approximately 5:00 A.M., the records are updated?


Ginger,

The "last payment" is the latest payment that is received from the patient. Several individuals within the dept. will monitor variances between what is expected from the patient in comparison with what has been received to date. Also, by comparing the "bill date" to the "date of the first payment", I will obtain a measure of which patients are delaying payments...

 

[cmmrfrds]

Once you have the various queries/sql setup then a Macro could run then in the sequence you want. The application could be launched from the windows scheduler.

 

[traingamer]

You can make it work in Access by combining 4 queries:

Get the last payment date: qMaxPay
SELECT t2.Acct, t2.EncNo, Max(t2.PaymentDate) AS MaxDate
FROM tblPayments AS t2
GROUP BY t2.Acct, t2.EncNo;

Get the last payment amount: qLstPayment

SELECT tblPayments.Acct, tblPayments.EncNo, tblPayments.PaymentDate, tblPayments.PymtAmt
FROM tblPayments INNER JOIN qMaxPay ON (tblPayments.PaymentDate=qMaxPay.MaxDate) AND (tblPayments.EncNo=qMaxPay.EncNo) AND (tblPayments.Acct=qMaxPay.Acct);

Get the first payment date: qMinDate

SELECT t2.Acct, t2.EncNo, Min(t2.PaymentDate) AS MinDate
FROM tblPayments AS t2
GROUP BY t2.Acct, t2.EncNo;

Get the first Payment Amount: qFirstPay

SELECT tblPayments.Acct, tblPayments.EncNo, tblPayments.PaymentDate AS FirstDate, tblPayments.PymtAmt AS FirstAmt
FROM tblPayments INNER JOIN qMinDate ON (tblPayments.PaymentDate = qMinDate.MinDate) AND (tblPayments.EncNo = qMinDate.EncNo) AND (tblPayments.Acct = qMinDate.Acct);

Combine them: qFirstLast

SELECT qFirstPay.Acct, qFirstPay.EncNo, qFirstPay.FirstDate, qFirstPay.FirstAmt, qLastPayment.PaymentDate, qLastPayment.PymtAmt
FROM qFirstPay INNER JOIN qLastPayment ON (qFirstPay.Acct = qLastPayment.Acct) AND (qFirstPay.EncNo = qLastPayment.EncNo);

output:
Acct EncNo FirstDate FirstAmt PaymentDate PymtAmt
AB1 136589 1/12/2005 $162.61 1/26/2005 $9,736.00
AB1 140856 11/24/2003 $327.85 2/1/2005 $82.80
AB2 136890 11/29/2004 $1,319.36 1/26/2005 $513.79

Ginger's question remains about the 'first' payment

traingamer

 

[SpeedThink]

traingamer,

Thanks for the insight. I will try the queries and learn about Windows scheduler by Wednesday afternoon.

As for Ginger's question - the first payment in this instance would be the first payment that interfaced the Oracle-based application. Note, there is a time stamp on the payment date. Of course, I used the "trunc" feature to reformat... Based on my review of the data, the instances where there are multiple payments on the same day is quite rare - approximately 3 to 5%. Nonetheless, I will inquire about this further.


Acct----EncNo----Pymt Amt-------Payment Date
AB1-----136589----162.61---------01/12/05
AB1-----136589----552.58---------01/12/05

 

[traingamer]

If you know how to write a nested passthrough query for Oracle, it'd probably be a lot faster than my example.

traingamer

 

[SpeedThink]

traingamer,

To ensure that I have the correct interpretation:

Is it possible to run a passthrough query to obtain data from an Oracle database within Microsoft Access using an ADO connection?

Also, in your first example, I assume that I would perform the queries within Access via an ADO connection??

I further assume that upon setting the queries to run automatically every day at 5:00 A.M., the length of time to perform the query would not be an issue.

I guess I need to familiarize myself with ADO connections, Oracle passthrough queries, etc.

 

[cmmrfrds]

The example shown is Access sql and presumes linked Oracle tables. The link is through an ODBC connection.

A pass through query is done through an ODBC connection. This is usually done if you want to bind a query to a Form or Report and want the query work to be done on the server.

An Oracle query through ADO is usually done to work directly with the data in a recordset in vba code. For example, you could loop through the recordset and add the records to an Access table.

For the small amounts of data you suggest about 10k records then linking the Oracle tables is the easiest and feasible option. For larger recordset, >100k then it will be better to pass the query to Oracle either with ODBC or ADO. ADO is the better option for working with recordsets in vba code.

 

[cmmrfrds]

One more comment. In later versions of Access there is a Recordset object on the Form and Report. Starting with Access 2000 there is a Form recordset but no Report recordset. My understanding that the later versions have a Report recordset.

So, you can run an ADO query and bind the results to the Form or Report recordset. This way you do not need to create an ODBC pass-through query to bind to the Form or Report.

 

[SpeedThink]

cmmrfrds,

Thanks for the insight.