Delete customer orders over two years old from their last order date-CodePudding

I need to delete records older than two years for individual customers.
I am looking for an efficent solution because of number of rows in my table.

In the simplified example below I am looking to delete record Order_ID o3 as it is over two years old from customer_id 1 last order date which is 01/01/2022

Following is a simple view of my table

Order_ID    Customer_ID     Order_Date  
o1          1               01/01/2022  
o2          2               02/02/2022  
o3          1               01/01/2019

delete as it is over two years fro Customer#1's last order

o4          1               01/01/2021  
o5          2               01/08/2021

CodePudding user response：

You could get the MAX date for each individual user in a subquery, and use the DATEADD function to get the date 2 years previous to the MAX (most recent order) date for each individual user and join on this to do deletions :

  delete t 
  from yourtable t
  inner join
  (
     select Customer_ID, DATEADD(year, -2, MAX(ORDER_DATE)) as order_date
     from yourtable
     group by Customer_ID
  ) x
  on t.Customer_ID = x.Customer_ID
  where t.ORDER_DATE < x.order_date

Sample Data:

Customer_ID ORDER_DATE
1           2019-06-13
1           2022-06-14
2           2022-01-01
2           2022-06-03
2           2019-01-01
3           2019-03-03

Result:

Customer_ID ORDER_DATE
1           2022-06-14
2           2022-01-01
2           2022-06-03
3           2019-03-03