David Undy David Undy - 1 month ago 5
SQL Question

SQL Get records past a certain date, higher than a certain value, with a minimum amount

I'm having a hard time with an SQL query at the moment. I have a list of customer orders, and I want to remove a set of them based on certain criteria:


  1. We need to keep at least 6 of each customers' past orders on hand.

  2. We need to keep all of the customers orders that occurred within the past 90 days.

  3. We need to keep AT LEAST 1 of each customers orders that is older than 90 days (if the customer had 4 orders in the past 90 days, we'll need to keep the 2 from an earlier time to hit the 6 orders requirement.



So, for example, if a customer had 6 orders in the past 90 days, we would keep 7 of their orders (because we include the 1 order from older than 90 days).

If a customer had 21 orders in the past 90 days, we would keep 22 of their orders.

If a customer had 5 orders in the past 90 days, we would keep 6 of their orders.

Here is the query I am using to build a table of their orders:

INSERT INTO @OrdersToDelete
SELECT TempOrders.Site, TempOrders.Number, TempOrders.RowNumber, TempOrders.CustomerNumber
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY CustomerNumber ORDER BY OrderDate DESC) AS 'RowNumber',
Number,
OrderDate,
CustomerNumber
FROM Orders
) TempOrders
LEFT OUTER JOIN (SELECT
ROW_NUMBER() OVER ( PARTITION BY CustomerNumber ORDER BY OrderDate DESC) AS 'RowNumber',
Number,
CustomerNumber
FROM SmartOrders
) SmartOrderOrders
ON TempOrders.Site = SmartOrderOrders.Site
AND TempOrders.Number = SmartOrderOrders.Number
WHERE
(DATEDIFF(dd, OrderDate, GETDATE()) > 90


This query returns a list of orders that are up for deletion (older than 90 days). In the WHERE clause, I can also check the order number, but I'm having difficulty figuring out how to exclude the customers first order after the 90 days period.

Any help would be appreciated.

vkp vkp
Answer
--Get the rownumbers using a case expression in order by 
--so all the orders within the last 90 days come first
WITH ROWNUMS AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY CustomerNumber 
                   ORDER BY 
                   CASE WHEN DATEDIFF(dd, OrderDate, GETDATE()) < 90 THEN 1 ELSE 0 END DESC, 
                   OrderDate DESC) AS 'RowNumber',
Number,
OrderDate,
CustomerNumber
FROM Orders) 
--Get the maximum rownumber per customer in the last 90 days
,MAXROWNUM AS (select CustomerNumber, MAX(rn) maxrn from ROWNUMS 
               where diff<=90 
               group by id)
--Join the previous cte's and get all the orders for a customer in the last 90 days
-- + one more row which is the latest before 90 days
SELECT r.* 
FROM ROWNUMS r
JOIN MAXROWNUM c ON c.CustomerNumber=r.CustomerNumber
WHERE r.rn <= c.maxrn+1 
--use r.rn <= case when c.maxrn <=5 then 5 else c.maxrn end + 1 to get atleast 6 orders per customer