1. Home
  2. Point-Of-Sale Software
  3. DELETE customer with no sales SQL help

DELETE customer with no sales SQL help

I have searched but cannot find the SQL script to delete customers with no sales.

I thought I saw it on here at one time.

Can anyone help?

The problem for me is, I have customers with WORK ORDERS waiting to be filled that I do not want to delete. Their work orders have been tendered but have not been turned into an invoice yet.

(This is to delete customers that I imported from my other system a while ago that haven't been back)

Thanks for any help.

So far, the select query I have is:

SELECT [Order].ID AS orderID, [Transaction].TransactionNumber, Customer.AccountNumber FROM [Transaction] RIGHT OUTER JOIN Customer ON [Transaction].CustomerID = Customer.ID LEFT OUTER JOIN [Order] ON dbo.Customer.ID [Order].CustomerID WHERE ([Transaction].TransactionNumber IS NULL) AND ([Order].ID IS NULL)

mickie

Reply to
Mickie
Loading thread data ...

When you execute your query - how many rows are returned...?

Reply to
jocelynp

20,726 which is about right. That was 4 years worth of customers I imported. It has been 1.5 years since we started RMS and the total count is 34269. (13543 difference)

Mickie

Reply to
Mickie

Oh Wait...I just noticed the TOTAL SALES field in the customer table. Is this updated every time a customer orders?

If that is the case, then

I just need to delete all customer with $0 total sales that were created before 10/1/2007.

delete customer from customer WHERE (TotalSales = 0) AND (AccountOpened < CONVERT(DATETIME, '2007-10-02 00:00:00', 102))

right?

Reply to
Mickie

Mickie,

If you are looking to delete all customers whose account was opened before

10/2/2007 and have zero total sales, your query would be - after backup:

DELETE FROM Customer WHERE AccountOpened < '2007-10-02' AND TotalSales = 0.00

This does not bring any of your earlier work order stuff into play.

Before you run your delete query, you might want to run a select against those customers, export and archive the data incase you need it later:

SELECT * FROM Customer WHERE AccountOpened < '2007-10-02' AND TotalSales 0.00

Initially the reason why I asked about how many customers were returned was because sometimes when I am working with a complicated Select query, I like to export the contents to a mail merge and merge in my action statements based on the contents. This would have been pretty fun if you were going the route with the work orders.

Good luck and let me know how things work out. Have a great day!!!

Reply to
jocelynp

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.