• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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
0
sqlcurious
Asked:
sqlcurious
1 Solution
 
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
 
PortletPaulCommented:
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
 
sqlcuriousAuthor Commented:
thanks
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now