query for a clients who have not purchased with us in over 1 year.

Posted on 2013-07-01
Medium Priority
Last Modified: 2013-07-11
Hi experts I need to do a query for "clients who have not purchased with us in over 1 year"
They have to be customers who have purchased an item before a year with us but havent purchased since one year,  pls help.
There is a table which has customer_no, item, datepurchased
Question by:sqlcurious
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39291680
select * from table
where dateadd(y, -1, getdate()) > datepurchased
and customer_no not in
(select distinct customer_no from table where datepurchased > dateadd(y, -1, getdate()) )

replace table with the table name.
LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39292175
I'd recommend a group by as it permits other useful metrics (such date of last purchase) and then filter the result via the having clause
    , max(datepurchased)  AS last_purchase_dt
    , count(*)            AS num_purchases
FROM YourTable
    max(datepurchased) <= dateadd(YEAR, -1, getdate())

Open in new window


Author Closing Comment

ID: 39319102

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 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