?
Solved

Oracle - Return Accounts not present for 10 days

Posted on 2014-03-12
9
Medium Priority
?
442 Views
Last Modified: 2014-03-12
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.
0
Comment
Question by:cottenmouth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39923666
>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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923670
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39923755
What version of Oracle are you using (select * from v$version)?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:cottenmouth
ID: 39924398
I would say that it needs to return records where the account was not in the transaction table 10 days prior to todays date.
0
 

Author Comment

by:cottenmouth
ID: 39924405
@awking00 its version 11.1.0.7.0
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39924485
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 )
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39924550
"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.
0
 

Author Comment

by:cottenmouth
ID: 39924837
Excellent! Thank you all for the feed back and @Guy Hengel for the solution, works perfectly!
0
 

Author Closing Comment

by:cottenmouth
ID: 39924843
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!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question