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.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>for 10 concecutive days.
for ANY 10 days? or just the last 10 days?
what would be the relevant "start date"?
or do you want to consider only those that have at least 1 transaction?

presuming that, I would go with this as a starter:
select a.*, t.transaction_date
  from account_table a
  join transaction_table t
    on t.accountnumber = a.accountnumber
   and t.transaction_date < trunc(sysdate -10 )
  where not exists ( select null from transaction_table n
                                  where n.accountnumber = a.accountnumber
                                     and n.transaction_date > t.transaction_date
                                     and n.transaction_date <= t.transaction_date + 10

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
What version of Oracle are you using (select * from v$version)?
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

cottenmouthAuthor Commented:
I would say that it needs to return records where the account was not in the transaction table 10 days prior to todays date.
cottenmouthAuthor Commented:
@awking00 its version
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Select * from account_table a
Where not exists( select null from transactions_table t where t.account = a.account and >= trunc (sysdate)-10 )
Mark GeerlingsDatabase AdministratorCommented:
"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.
cottenmouthAuthor Commented:
Excellent! Thank you all for the feed back and @Guy Hengel for the solution, works perfectly!
cottenmouthAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.