Solved

Oracle - Return Accounts not present for 10 days

Posted on 2014-03-12
9
440 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 500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

717 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