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

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
Rhonda CarrollAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
See attached.
customers.txt
0
 
sdstuberCommented:
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
 
OMC2000Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rhonda CarrollAuthor Commented:
It is actually a date field and has been around for many years. it must be just the way it is viewed.
0
 
DrSQLCommented:
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
 
DrSQLCommented:
Rhonda,
   Sorry, I just saw the clarification on the date field.  Just ignore the to_date.  The rest should apply.

Good luck!
0
 
sdstuberCommented:
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
 
DrSQLCommented:
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
 
sdstuberCommented:
ah, now that I see the answer, I understand the question.   :)

Thanks for clarifying awking00, good work!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.