David Undy - 3 months ago 11

SQL Question

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:

- We need to keep at least 6 of each customers' past orders on hand.
- We need to keep all of the customers orders that occurred within the past 90 days.
- 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.

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
```