943,954 Members | Top Members by Rank

Ad:
  • Oracle Discussion Thread
  • Marked Solved
  • Views: 793
  • Oracle RSS
Jul 23rd, 2009
0

Oracle query help

Expand Post »
I have to create a report based on the customer for particular order.
For example find all the customers who ordered an item ‘ABC’ for current month
and look back 11 months to find out if the same customers ordered the same item
and list all the customers who ordered ‘ABC’ in last 11 months

If 1000 customers ordered an item ‘ABC’ in June 09.
Check if any of the 1000 customers ordered ABC from July 08 to May 09.
If any list the customer.

Can you plz help me write the query for this? Thanks,
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
mamtha is offline Offline
7 posts
since Feb 2008
Jul 23rd, 2009
0

Re: Oracle query help

I think we would need a little more details to what you are asking.

Do you want a list of all customers that purchased a specific product in the current month only IF they had purchased the same product in the past 11 months?

The product will be a provided parameter?

It also sounds like this information would be held in multiple tables, are you not interested in how to pull these together; just how to make this comparison?

If you need to know how to join the tables a schema description of the tables would be required.
Reputation Points: 91
Solved Threads: 18
Junior Poster
cgyrob is offline Offline
125 posts
since Sep 2008
Jul 23rd, 2009
0

Re: Oracle query help

Right, list the customers if they purchased the same product in june as well as in previous 11 months.
The data is only in one table and don't need to join other tables.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
mamtha is offline Offline
7 posts
since Feb 2008
Jul 23rd, 2009
0

Re: Oracle query help

Try something like this

sql Syntax (Toggle Plain Text)
  1. SELECT customer FROM Table
  2. WHERE purchasedate BETWEEN trunc(SYSDATE,'MM') AND LAST_DAY (TO_DATE (trunc(SYSDATE,'MM')))
  3. AND product = 'ABC'
  4. INTERSECT
  5. SELECT customer FROM table
  6. WHERE purchasedate BETWEEN add_months(trunc(SYSDATE,'MM'),-11) AND trunc(SYSDATE,'MM')-1
  7. AND product = 'ABC'

This will only show a customer that has purchased a particular product in the current month as well as in the last 11 months.
Last edited by cgyrob; Jul 23rd, 2009 at 12:19 pm.
Reputation Points: 91
Solved Threads: 18
Junior Poster
cgyrob is offline Offline
125 posts
since Sep 2008
Jul 23rd, 2009
0

Re: Oracle query help

Thanks!
I was trying the sub query..
This looks like a faster version.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
mamtha is offline Offline
7 posts
since Feb 2008
Jul 23rd, 2009
0

Re: Oracle query help

Yes intersects work well and are much more readable.
Reputation Points: 91
Solved Threads: 18
Junior Poster
cgyrob is offline Offline
125 posts
since Sep 2008
Jul 23rd, 2009
0

Re: Oracle query help

Mamtha,

If this solved your question can you please mark the thread solved.

thanks.
Reputation Points: 91
Solved Threads: 18
Junior Poster
cgyrob is offline Offline
125 posts
since Sep 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Oracle Forum Timeline: Create / Delete User account
Next Thread in Oracle Forum Timeline: Why Oracle or other Databases?





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC