asked on

Oracle - Return Accounts not present for 10 days

I have 2 tables:

1. Contains account numbers and relevant information
2. contains transaction level data

I need to join the two tables and provide accounts numbers that are not present in the transaction level table for 10 concecutive days.

I'm working in an oracle environment.

Thanks for any assistance you can provide.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

to notice that a index on accountnumber + transaction_date on the transaction_table is "required" to avoid the query would take ages on big tables

awking00

What version of Oracle are you using (select * from v$version)?

cottenmouth

ASKER

I would say that it needs to return records where the account was not in the transaction table 10 days prior to todays date.

cottenmouth

ASKER

@awking00 its version 11.1.0.7.0

Guy Hengel [angelIII / a3]

Select * from account_table a
Where not exists( select null from transactions_table t where t.account = a.account and t.date >= trunc (sysdate)-10 )

Mark Geerlings

"I need to ... provide account numbers that are not present in the transaction level table for 10 concecutive days."

That kind of report (basically: what is *NOT* in the database, or *NOT* in a particular table) is always much harder to write in SQL and usually more resource-intensive for Oracle to execute, than typical reports of data that *IS* in the database.

The suggestion from Guy Hengel with the "Where not exists" sub-query should do the job as efficiently as possible for you.

cottenmouth

ASKER

Excellent! Thank you all for the feed back and @Guy Hengel for the solution, works perfectly!

cottenmouth

ASKER

This solution worked perfectly, i was able to limit the date range for the records to the beginning of a month and the query ran in a matter of a few minutes.

Thanks again!