Prev: Find distinct records of the last order from a customer
Next: Insert a one returned value from a MAX function in SSRS in Footer
From: David on 25 Jun 2010 10:36
Hi

I am trying to find a list of customers that have not bought anything
for the last xx months.

I have a salesorder table which holds an orderdate and customer
account code, other customer details are held in the customers table.
i have a table called soitemsdespatch thats holds the parts that have
been sold to that customer. salesorder and soitemsdespatch are linked
by sonumber.

I was hoping that would show me the last orderdate for all customers
and list them in order but it repeats customer orders but not all of
them.
What i would really like is one query that would show me all the
customers that have not bought anything between two dates :-)

below is the query - where am I going wrong? Any help or advice would
be greatly appreciated.

thank you

select max(s.orderdate), s.customercode,
so.Customer,Telephone,email
from salesorder s
inner join soitemsdespatch so on so.sonumber=s.sonumber
inner join customers c on c.customercode = s.customercode
where s.OrderDate = (select max(s2.orderdate)
from salesorder s2
inner join soitemsdespatch so2 on so2.sonumber=s2.sonumber
inner join customers c2 on c2.customercode =
s2.customercode
where so2.sonumber=so.sonumber)
group by s.orderdate, s.customercode, so.Customer,Telephone,email
order by s.orderdate




 | 
Pages: 1
Prev: Find distinct records of the last order from a customer
Next: Insert a one returned value from a MAX function in SSRS in Footer