SQL Get records past a certain date, higher than a certain value, with a minimum amount
<p>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:</p>

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

<p>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).</p>

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

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

<p>Here is the query I am using to build a table of their orders:</p>

<pre><code>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()) &gt; 90
</code></pre>

<p>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.</p>

<p>Any help would be appreciated.</p>