Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle - Return Accounts not present for 10 days

Posted on 2014-03-12
9
Medium Priority
?
444 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

618 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