Solved

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

Posted on 2013-07-01
3
352 Views
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
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

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.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 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
SELECT
      customer_no
    , max(datepurchased)  AS last_purchase_dt
    , count(*)            AS num_purchases
FROM YourTable
GROUP BY
    customer_no
HAVING
    max(datepurchased) <= dateadd(YEAR, -1, getdate())

Open in new window

0
 

Author Closing Comment

by:sqlcurious
ID: 39319102
thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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