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

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
sqlcuriousAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
sqlcuriousAuthor Commented:
thanks
0
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.

All Courses

From novice to tech pro — start learning today.