Business year end for accounting

[burt]

Try this (UNTESTED);

Code: Select all

SELECT customers_name, o.customers_email_address, MAX(date_purchased) as last_purchased_on FROM orders o group by customers_id

this is grouping by the customers_id (ie, shows only 1 result per customer) and grabbing the last time they purchased.

[Kofod95]

Another option would be:

Code: Select all

SELECT c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address 
FROM customers c LEFT OUTER JOIN orders o ON c.customers_id = o.customers_id
WHERE o.orders_id IS NULL 
OR o.date_purchased NOT BETWEEN DATE_SUB(NOW(), INTERVAL 6 YEAR) AND NOW() 
GROUP BY c.customers_id

This will (I think) fetch the id's, names and email-addresses of customers who haven't bought anything in the last six years.

To delete them you would of course have to add address_book, customers_info, customers_basket and so on. On that note: What would be done with reviews, if any? Make a 'dummy' customer to assign the review to, to prevent losing the reviews?

//Daniel

[ecartz]

Code: Select all

SELECT c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address 
FROM customers c LEFT OUTER JOIN orders o ON c.customers_id = o.customers_id
WHERE o.orders_id IS NULL 
OR o.date_purchased NOT BETWEEN DATE_SUB(NOW(), INTERVAL 6 YEAR) AND NOW() 
GROUP BY c.customers_id

This will (I think) fetch the id's, names and email-addresses of customers who haven't bought anything in the last six years.

I don't think so. I think that it will return

1. Customers who have no orders.
2. Customers who have at least one order older than six years, regardless of how many orders they may have had since then.

Maybe:

Code: Select all

SELECT c.customers_firstname, c.customers_lastname, c.customers_email_address 
FROM customers c LEFT OUTER JOIN orders o ON c.customers_id = o.customers_id
  AND o.date_purchased >= DATE_SUB(NOW(), INTERVAL 6 YEAR)
WHERE o.orders_id IS NULL 

I think that returns everyone who does not have an order in the last six years.

[14Steve14]

Code: Select all

SELECT c.customers_firstname, c.customers_lastname, c.customers_email_address 
FROM customers c LEFT OUTER JOIN orders o ON c.customers_id = o.customers_id
  AND o.date_purchased >= DATE_SUB(NOW(), INTERVAL 6 YEAR)
WHERE o.orders_id IS NULL 

I think that returns everyone who does not have an order in the last six years.

This seems to get the nearest result that I am after. I gives a list of just over 500 customers who have not placed an order within 6 years. Looking at the dates of these last orders, and the way our accounting year is in April I adjusted the six years to seven years to be on the safe side, and the list reduced to 450ish. For some reason there are a few customers on the end of the list that accounts were only created this year but they have not placed an order, but they are easy to remove, and they are the same as those in our inactive customers list.

I did add into the sql , c.customers_newsletter just after email_address and that showed which customers have signed up for newsletters so we should be able to contact those customers to let them know what is happening and why.

Does anyone see any problems with deleting these customers and their data and orders? I know it will affect what I see in my admin backend where sales reports are concerned, but as its so old data is there any need to keep data that old as it has no real benefit.

[Omar_one]

This seems to get the nearest result that I am after. I gives a list of just over 500 customers who have not placed an order within 6 years. Looking at the dates of these last orders, and the way our accounting year is in April I adjusted the six years to seven years to be on the safe side, and the list reduced to 450ish. For some reason there are a few customers on the end of the list that accounts were only created this year but they have not placed an order, but they are easy to remove, and they are the same as those in our inactive customers list.

I did add into the sql , c.customers_newsletter just after email_address and that showed which customers have signed up for newsletters so we should be able to contact those customers to let them know what is happening and why.

Does anyone see any problems with deleting these customers and their data and orders? I know it will affect what I see in my admin backend where sales reports are concerned, but as its so old data is there any need to keep data that old as it has no real benefit.

maybe keep the customers whos have been login on the nears year or so ,, that's mean they still have interest to buy your products, maybe adding to query customers_info_date_of_last_logon