Generating an SQL query to list previously regular clients that have not returned in the last six months
Posted on 2007-08-02
I need to run an SQL query on my database for marketing purposes. I have all of our invoice history for the past 4 years stored in a table in a mySQL database. What we are wanting to accomplish is to produce a report of all customers who have spent over $200 with us in the last two years but that have not spent any money in the last 6 months.
The relevant table structure for the database table is as follows:
invoice_client_id (unique ID # for the client, this is consistent on all invoices for that client)
invoice_total (total amount spent for that invoice)
invoice_date (Date of Invoice)
Basically, what I want to do is to get a total spent for each client [which should be able to be done by doing SELECT `invoice_client_id`,SUM(`invoice_total`) FROM `invoices` GROUP BY `invoice_client_id`]. However, I need to expand that further and then only get results for clients who spent AT LEAST $200, In the last 2 years. Furthermore, if possible not include clients who have made purchases in the last six months.
Basically the idea is I want to get a list of people who spent a good amount of money with my company during this past 2 years but that have not come back in the last six months.
I know I could do this in php/mysql with multiple queries but I didn't know if there was another way I could do it with strictly a single mySQL query.
I DO NOT have this now, but what I may add to make this easier later is to add a "client_last_invoiced_date" column in my database of client information, which would then allow me to generate a list of clients whose last invoice date was more than six months ago. If THIS is the easiest way to go about what I am doing, if someone could provide me with an example of doing it that way.
Assume that my Client's table is as follows: