Prev: Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle
Next: Find distinct records of the last order from a customer
From: David on 25 Jun 2010 10:20
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




From: Erland Sommarskog on 25 Jun 2010 11:00
David (david(a)thechameleongroup.co.uk) writes:
> I am trying to find a list of customers that have not bought anything
> for the last xx months.

SELECT ...
FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID
AND O.OrderDate > dateadd(month, -6, getdate())

But this does not really agree with the Subject line for your
post, not with the query you posted, so it's a little unclear
what you are really looking for.

The second half of the football is about to start, so I need to dash.
But which version of SQL Server are you using?


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: David on 28 Jun 2010 04:54
Hi
Thanks for the response, I am using SQL 2005.

I have taken your query, run it and tested it against the data and
although the results were quite 'scary' in so far that alot of
customers have not bought anything for while it seems to satisfy the
sales directors requirements. So sorry for not seeming clear but you
manged to solve it ayway so more to power to you! :-)

thank you for your time and help and I hope you enjoyed the second
half!

kind regards
David
 | 
Pages: 1
Prev: Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle
Next: Find distinct records of the last order from a customer