Generating an SQL query to list previously regular clients that have not returned in the last six months

Posted on 2007-08-02
Last Modified: 2013-12-12
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:
Question by:gopcinc
    LVL 45

    Accepted Solution


    Hi gopcinc,

    Piece of cake...   :)

    SELECT T0.*, T1.sum6
      SELECT client_id, sum(invoice_amount)  as sum24
      FROM invoices
      WHERE invoice_date > date_add (current_date, interval -2 years)
    ) T0
      SELECT client_id, sum(invoice_amount) as sum6
      FROM invoices T0
      WHERE invoice_date between date_add (current_date, interval -6 months)
    ) T0
      ON T0.client_id = T1.client_id
    WHERE sum24 >= 200
      AND sum6 = 0;

    Good Luck,
    LVL 24

    Assisted Solution

    I think you can condense that a bit-

    SELECT invoice_client_id id, sum(invoice_total)  MoneySpent, max(invoice_date) lastTrans
      FROM invoices
      WHERE invoice_date > date_add (current_date, interval -2 years)
      HAVING lastTrans < date_add(current_date, interval -6 months) AND MoneySpent>200
    LVL 24

    Expert Comment

    You could create the table you were thinking of by doing this

    update clients set client_id = (select max(invoice_date) from invoices where clients.client_id=invoices.invoice_client_id)

    LVL 24

    Expert Comment

    The only problem with that is that you would have to dynamically update the last transaction time (maybe with stored proc) each time you had a transaction.
    LVL 45

    Expert Comment

    Hi mankowitz,

    Nice code.  :)

    I don't know the poster's business model, but answering his original question in my shop would require SQL similar to what I posted.  If the table holds zero dollar events or records for returns then events in the last 6 months aren't necessarily purchases and a client may be artificially skipped.

    Still, if he's looking for activity (instead of sales) during the past 6 months, your code is spot on.

    LVL 19

    Expert Comment

    Try this link
    Register that is a sql server helper they usually respond imidiately or try to use LazydbaDiscussion they sent respond via email

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Write an app 9 36
    PHP foreach DB query 4 21
    How do companies protect source code? 4 33
    Image not there 4 8
    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now