Home » SQL & PL/SQL » SQL & PL/SQL » Finding old records
Finding old records [message #299925] Wed, 13 February 2008 12:59 Go to next message
mamit1
Messages: 4
Registered: January 2008
Junior Member
Hi all,
Need a little help with a simple SQL query. for some reason having a mind block. I need to select customers from a table who have not placed orders in lets say last one year. A crude way of doing this would be to select all customers and then remove the ones who have placed orders in the last year but this means big database load. can someone show me how to do this with a EXIST or a NOT EXIST.

EG:

SELECT rc.customer_id, rc.customer_name,max(ooha.ordered_date)
FROM oe_order_headers_all ooha, ra_customers rc
WHERE rc.customer_id = ooha.sold_to_org_id
GROUP BY rc.customer_id, rc.customer_name
MINUS
SELECT rc.customer_id, rc.customer_name,max(ooha.ordered_date)
FROM oe_order_headers_all ooha, ra_customers rc
WHERE ooha.ordered_date > SYSDATE - TO_YMINTERVAL('01-00')
AND rc.customer_id = ooha.sold_to_org_id
GROUP BY rc.customer_id, rc.customer_name

Thanks
Matt
Re: Finding old records [message #299927 is a reply to message #299925] Wed, 13 February 2008 13:15 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
What about using HAVING.

Michael
Re: Finding old records [message #299930 is a reply to message #299927] Wed, 13 February 2008 13:20 Go to previous messageGo to next message
mamit1
Messages: 4
Registered: January 2008
Junior Member
I can use Having but can you please show me how for the above example (if you dont mind)
Re: Finding old records [message #299931 is a reply to message #299930] Wed, 13 February 2008 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You search the customers that have their max order date less than one year ago.

Regards
Michel
Re: Finding old records [message #299933 is a reply to message #299931] Wed, 13 February 2008 13:28 Go to previous messageGo to next message
mamit1
Messages: 4
Registered: January 2008
Junior Member
Michel Cadot wrote on Wed, 13 February 2008 13:23
You search the customers that have their max order date less than one year ago.

Regards
Michel




I Michel I need to find customer who have NOT placed order in past one year so max order date should be more than a year ago right?
Re: Finding old records [message #299934 is a reply to message #299933] Wed, 13 February 2008 14:08 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arithmetically speaking:
date less than one year ago: date < sysdate-one year
date more than one year age: date > sysdate-one year.

Regards
Michel

[Updated on: Wed, 13 February 2008 14:09]

Report message to a moderator

Previous Topic: Create Calendar w/ sql
Next Topic: PLSQL Table Arrays
Goto Forum:
  


Current Time: Wed Dec 07 12:40:38 CST 2016

Total time taken to generate the page: 0.12747 seconds