Solved

Oracle - Return Accounts not present for 10 days

Posted on 2014-03-12
9
431 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
9 Comments
 
LVL 142

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 142

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 31

Expert Comment

by:awking00
ID: 39923755
What version of Oracle are you using (select * from v$version)?
0
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now