Solved

How can I use add_months with a date field to retrieve past data?

Posted on 2008-06-26
9
545 Views
Last Modified: 2012-08-14
Hi,

I need to be able to retrieve invoices from the invoice table for the past month for customers who haven't received an invoice in 6 months. I have been trying to use sysdate with add_months but i can't pull any data back.

This is the invoice_date field data format 4/30/2001

Thansk,
R
0
Comment
Question by:Rhonda Carroll
9 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 21876685
so, your invoice_date isn't actually a date?  It's a string?
you should get that fixed right away if possible.
first though, confirm it really is a string,  it's also quite likely it is a date and you're simply viewing it in that format.

However, to address your immediate concern,  I'm not sure how you can retrieve any data.
If you have a customer that hasn't been invoiced in 6 months, then how are they going to have a record in the invoice table for the previous month.  

Maybe I'm not understanding your question.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 21876722
I guess it must be something like the following:

select * from invoices
where to_date(invoice_date,'mm/dd/yyyy') > add_months(sysdate,-6)
and received is null

where "received"  field is one, which confirms receiving of invoice by a customer
0
 

Author Comment

by:Rhonda Carroll
ID: 21876727
It is actually a date field and has been around for many years. it must be just the way it is viewed.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21876764
Rhonda,
    To use add_month to go back you use negative numbers:

select * from invoices
where custid in
(select custid from invoices
MINUS
select custid from invoices where to_date(invoice_date,'MM/DD/YYYY') >= add_months(sysdate,-6))
/

You want to eliminate the customers who've had an invoice in the last six months.  That's done by using a minus operation here.

Good luck!
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 22

Expert Comment

by:DrSQL
ID: 21876777
Rhonda,
   Sorry, I just saw the clarification on the date field.  Just ignore the to_date.  The rest should apply.

Good luck!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21876956
sounds like you need more than one column

how do you find an invoice row that is for the previous month?
If that's invoice_date, then what is the difference between a row from last month and
all other rows in the last 6 months?  
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21877038
Rhonda,
    Do you mean you want to get a list of the customers who have an invoice in the last month, but it's the first one they've gotten in six months?  That would be just a minor change to the query I gave you:

select custid, min(invoice_date) earliest_invoice_this_month,count(*) numberInvoices
from invoices
where invoice_date > add_months(sysdate,-1) and
custid in
(select custid from invoices where invoice_date > add_months(sysdate,-1)
MINUS
select custid from invoices where to_date(invoice_date,'MM/DD/YYYY') >= add_months(sysdate,-6))
group by custid
/

Good luck!
0
 
LVL 32

Accepted Solution

by:
awking00 earned 125 total points
ID: 21877916
See attached.
customers.txt
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21973239
ah, now that I see the answer, I understand the question.   :)

Thanks for clarifying awking00, good work!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

863 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

25 Experts available now in Live!

Get 1:1 Help Now