Solved

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

Posted on 2008-06-26
9
534 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Accepted Solution

by:
awking00 earned 125 total points
Comment Utility
See attached.
customers.txt
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
ah, now that I see the answer, I understand the question.   :)

Thanks for clarifying awking00, good work!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 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

8 Experts available now in Live!

Get 1:1 Help Now