Oracle query help

[mamtha]

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,

[cgyrob]

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.

[mamtha]

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.

[cgyrob]

Try something like this

Select customer from Table
Where purchasedate between trunc(sysdate,'MM') and LAST_DAY (TO_DATE (trunc(sysdate,'MM')))
and product = 'ABC'
INTERSECT
Select customer from table 
where purchasedate between add_months(trunc(sysdate,'MM'),-11) and trunc(sysdate,'MM')-1
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.

[mamtha]

Thanks!
I was trying the sub query..
This looks like a faster version.

[cgyrob]

Yes intersects work well and are much more readable.

[cgyrob]

Mamtha,

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

thanks.