[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-08-02
6
Medium Priority
?
264 Views
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:
client_id
client_name
client_last_invoiced_date
0
Comment
Question by:gopcinc
  • 3
  • 2
6 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1400 total points
ID: 19620334

Hi gopcinc,

Piece of cake...   :)



SELECT T0.*, T1.sum6
FROM
(
  SELECT client_id, sum(invoice_amount)  as sum24
  FROM invoices
  WHERE invoice_date > date_add (current_date, interval -2 years)
) T0
LEFT JOIN
(
  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,
Kent
0
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 600 total points
ID: 19622205
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
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 19622213
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)

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 24

Expert Comment

by:mankowitz
ID: 19622218
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.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 19624038
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.



Kent
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 19624783
Try this link
wwww.sqlservercentral.com
Register that is a sql server helper they usually respond imidiately or try to use LazydbaDiscussion they sent respond via email
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month19 days, 20 hours left to enroll

872 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