Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-07-01
3
Medium Priority
?
365 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 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.
0
 
LVL 49

Accepted Solution

by:
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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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