Maybe EXISTS, maybe not ? 1

[WurzelGummidge]

Hi,

I want to do the following but can't decide / think what the simplest Sql statement would be to use:

I have an 'ORDERS' table with fields:
CUSTOMER,ORDER_DATE,PRODUCT

Data could look like this:

CUST1 03/01/03 ABC123
CUST1 17/03/03 ABC123
CUST1 23/11/01 XYZ321
CUST2 09/02/02 ABC123
CUST3 12/12/02 QRS123
CUST3 19/09/03 XYZ123

What i would like to do is to return (distinct) customers that HAVE ordered this year, DID NOT order last year, but DID order the year before that!

Hope this is understandable

 

[swampBoogie]

select distinct customer from orders as c
 where exists 
  (select * from customer 
      where customer = c.customer
        and year(order_date) = 2003)
 and not exists 
(select * from customer 
      where customer = c.customer
        and year(order_date) = 2002)
 and exists
(select * from customer 
      where customer = c.customer
        and year(order_date) = 2001)

 

[r937]

[tt]select distinct customer
from orders O
where year(order_date)
= year(getdate())
and NOT EXISTS
( select 1 from orders
where customer = O.customer
and year(order_date)
= year(getdate()) - 1 )
and EXISTS
( select 1 from orders
where customer = O.customer
and year(order_date)
= year(getdate()) - 2 )[/tt]

rudy

http://r937.com/

 

[WurzelGummidge]

Thanks you guys - both gave exactly correct SQL - works a treat at about 2000 times quicker than the original Crystal Report I had running to give me this