SQL Plus - Insert into Oracle table under latest effective date 1

[LevelThought]

Have a draft copy of SQL script to be used in SQLplus to insert missing dept/charge code combinations into Oracle (version 8.1.7) table under the latest effective date for accounts 'ABC4' and 'ABC5':

Draft copy of SQL Script

insert into Contract_Details
(effective_date, patient_type, dept, charge_code, noncovered_type, amount, date_updated, user_updated, function_code, date_inserted, user_inserted, insert_function)
select cnd.effective_date, ’I’,chg.dept, chg.charge_code, ‘% of charge’, ‘100’, trunc(sysdate), 'jutow',’UpdConDet’,trunc(sysdate), ‘jutow’, ‘UpdConDet’)
from contract_details cd, chargelog chg
where cd.account_id in ('ABC4’,’ABC5’)
and chg.dept = ‘&dept’
and chg.charge_code = ‘&Charge Code’
and cd.effective_date = (select max(cnd.effective_date)
from contract_Details cd)
and cd.dept = chg.dept

Example of Contract_Details table

Acct_ID---Eff_Date----PT--Dept--ChgCode--NC Type--------Amt
ABC4------01-Jan-1999-I---7458--850062---% of charge----100
ABC4------01-Jan-1999-I---7458--850063---% of charge----100
ABC4------01-Dec-2005—I---7454--850066---% of charge----100
ABC4------01-Dec-2005-I---7450--850061---% of charge----100
ABC5------15-Mar-2004-I---5650--254634---% of charge----100
ABC5------15-Oct-2005-I---4563--254635---% of charge----100
ABC5------15-Oct-2005-O---4563--254635---% of charge----100

What modifications to the SQL script do I need to make to ensure that I enter the missing dept/charge codes into the Oracle table under the latest effective date for Accounts 'ABC4' and 'ABC5'?

For example, I would be interested in entering the following dept/charge combinations for account 'ABC4' for effective date of '01-Dec-2005':

2120 415263
2121 415264


Thanks in advance for any insight.

 

[PHV]

and cd.effective_date = (select max(cnd.effective_date)
from contract_Details cnd WHERE cnd.account=cd.account)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LevelThought]

oops! The "cd" and the "cnd" table are the same. I mistyped - all instances of "cnd" in the sql script should have been "cd."

The effective dates for the accounts are already within the "cd" table. Currently, I have to manually enter each new dept/charge code under the 20 accounts within the Contract_Details table for the latest effective date!

 

[LevelThought]

I assume that the sql script would be modified to something similar to:

"and cd.effective_date = (select max(cd.effective_date)
from contract_Details cd1 WHERE cd1.account=cd.account)"

 

[PHV]

and cd.effective_date = (select max(cd1.effective_date)
from contract_Details cd1 WHERE cd1.account=cd.account)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886