David Undy - 1 year ago 44

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 Source

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