?
Solved

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

Posted on 2008-06-26
9
Medium Priority
?
577 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
[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 74

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
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

 
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
 
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 74

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 500 total points
ID: 21877916
See attached.
customers.txt
0
 
LVL 74

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 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