# Iterating through a record set and selecting certain records

I am tasked with automating our item return process at work. Our policy for returns is to take the average price of the most recently purchased items desired to be returned.

We are effectively calculating a weighted average unit price of the most recently purchased items, excluding items from the average price once we have hit the desired return quantity.

I currently have a table in Microsoft SQL Server that contains the purchase history of all items (\$, units, and purchase date) that have been asked to be returned, and the desired quantity to be returned.

I'll add a specific example; 18 units of Item number 138799528 are to be returned (NOTE that the table is already ordered by `ItemNumber`, `CreatedDateTime` descending). In order to determine the return price, we need the query to start with the most recently purchased item (line 137) and select records until the sum of the `POQuantity` is equal to or greater than the desired return quantity. In this case, the proper item selection would be rows 137,138 and 139, while excluding the rest of the purchases of Item 138799528. The query continues until it has iterated through all items in the record set.

I am attempting to iterate over this table and select the appropriate records into a another table with which to calculate the average. I have attempted using a cursor but my DBA has insisted to avoid cursors at all costs. Below is the cursor logic:

``````    DECLARE ReturnCursor CURSOR

FOR SELECT
T.ItemNumber
,-(T.ReturnQuantity)
,T.POQuantity
,T.PurchasePrice

FROM #tableforcalc T
ORDER BY T.ItemNumber, T.CreatedDateTime DESC           --Added order by so following logic works when looping through records

OPEN ReturnCursor

SET @Counter=0
SET @Item2=''           --Needed to set Item2 = ''

FETCH NEXT FROM ReturnCursor
INTO @Item, @ReturnQty, @POQty, @PurchPrice

WHILE @@FETCH_STATUS=0
IF @Item2!=@Item
BEGIN

SET @Counter+=@POQty ;
SET @Item2=@Item;

SELECT

@Item AS ID
,@POQty AS qty
,@PurchPrice AS price
,@ReturnQty AS RQ

--INTO #Temp1               --  Put selected variables into a temp table

END

ELSE                --this else condition is essentially  IF @Item2=@Item

BEGIN

IF (@Counter<@ReturnQty AND @Counter!=0)

BEGIN

SET @Counter+= @POQty;
SELECT
@Item AS ID
,@POQty AS qty
,@PurchPrice AS price
,@ReturnQty AS RQ

--INTO #Temp2
END

ELSE

BEGIN

SET @Counter=0;

END
``````

I was wondering if there is a way to solve this problem using set operations in SQL? I am open to all suggestions.

## 2 Answers

I'm going to assume that the screenshot in the question is the result of a join and that you have separate tables for returns to be processed and your order history. Even if your data model doesn't match this exactly I imagine that it's not difficult to get it into a similar structure.

``````CREATE TABLE #ActiveReturns (
ItemNumber BIGINT,
ReturnQuantity NUMERIC(19,2)
);

INSERT INTO #ActiveReturns
VALUES
(138799528, -18);

CREATE TABLE #OrderHistory (
ItemNumber BIGINT,
POQuantity NUMERIC(19,2),
PurchasePrice NUMERIC(19,2),
CreatedDateTime DATETIME
);

INSERT INTO #OrderHistory
VALUES
(138799528, 4, 518.4, '20171102'),
(138799528, 8, 518.4, '20170606'),
(138799528, 12, 480, '20170126'),
(138799528, 8, 480, '20161115'),
(138799528, 8, 480, '20161114');

CREATE INDEX IXOH ON #OrderHistory (ItemNumber, CreatedDateTime)
INCLUDE (POQuantity, PurchasePrice);
``````

I loaded in some sample data but didn't want to type everything. For future questions you should consider defining your tables and providing code to load in sample data. You'll get better and faster answers if you don't require the people answering to look at images and type a bunch of stuff.

The two set-based solutions that come to mind are using `APPLY` or using window functions. I imagine that your returns table is much, much smaller than your order history table and your question hints that you have supporting indexing in place so I like the `APPLY` approach better as a first attempt.

The algorithm goes something like this:

• Start with a row from your returns table.
• Get the oldest relevant row from the order history table.
• Split that row into `POQuantity` rows. If you have at least `ReturnQuantity` rows then stop. Otherwise continue to get and split rows into you have enough.
• Only keep the first `ReturnQuantity` rows.
• Calculate the average of `PurchasePrice`.
• Get the next row from returns and process all rows in this way.

You can accomplish that in SQL with a join to a numbers table and the `TOP` operator. Here's a quick numbers table:

``````CREATE TABLE #numbers (num BIGINT NOT NULL, PRIMARY KEY (num));

INSERT INTO #numbers
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
``````

Here's the query I wrote to implement the algorithm:

``````SELECT ItemNumber, wa.weighted_average
FROM #ActiveReturns ar
CROSS APPLY (
SELECT SUM(t.PurchasePrice) / ABS(ar.ReturnQuantity)
FROM (
SELECT TOP (CAST(ABS(ReturnQuantity) AS BIGINT)) oh.PurchasePrice
FROM #OrderHistory oh
INNER JOIN #numbers n ON oh.POQuantity >= n.num
WHERE oh.ItemNumber = ar.ItemNumber
ORDER BY oh.CreatedDateTime DESC
) t
) wa (weighted_average);
``````

I get a weighted average of 505.60 which matches your expected results.

The query plan avoids processing unnecessary rows. As you can see in the query plan below, only three rows are read form the order history table and only 18 rows are read from the numbers table.

As I understand it you want to find records where running total of `POQuantity` is less than the `ReturnQuantity` values.

Windowing functions are ideal to help with this problem! See these resources for further info:

Ive mocked up a quick example from your data to demonstrate this:

``````DECLARE @Data TABLE(
ItemNumber            INT
,POQuantity            DECIMAL(20, 2)
,PurchasePrice        DECIMAL(20, 2)
,CreatedDateTime    DATETIME2(3)
,ReturnQuantity        DECIMAL(20, 2)
)

INSERT INTO @Data
VALUES
(138799528, 4, 518.40, '2017-11-02 09:43:18', -18)
,(138799528, 8, 518.40, '2017-06-06 15:15:00', -18)
,(138799528, 12, 480.00, '2017-01-26 15:52:33', -18)
,(138799528, 8, 450.00, '2016-11-14 11:36:42', -18)
,(100, 3, 100.00, '2017-11-02 09:43:18', -4)
,(100, 7, 150.00, '2017-10-02 19:30:12', -4)
;

WITH CalculateRunningTotals AS (
SELECT
ItemNumber
,POQuantity
,PurchasePrice
,CreatedDateTime
,ABS(ReturnQuantity)    AS ReturnQuantity    /* Make it a positive number for easier calculations later */
,SUM(POQuantity) OVER (PARTITION BY ItemNumber ORDER BY CreatedDateTime DESC)    AS RunningTotalQuantity
FROM
@Data
), FindApplicableData AS (
/* We need to consider the previous running total to make sure
we get "at least" the total POQuantity.  Otherwise we miss the
last row needed.
*/
SELECT
*

,CASE
WHEN RunningTotalQuantity <= ReturnQuantity THEN POQuantity
ELSE ReturnQuantity - (RunningTotalQuantity - POQuantity)
END AS POQuantityForAverages    /* Calculate how many "POQuantity" of this row are needed to make up the ReturnQuantity */
,RunningTotalQuantity - POQuantity AS PreviousRunningTotalQuantity

FROM
CalculateRunningTotals
WHERE
ABS(ReturnQuantity) >= (RunningTotalQuantity - POQuantity)    /* Compare to previous running total - remove this to see why you need it ;-) */
)
SELECT
ItemNumber
/* Calculate the average price - its not a simple AVG as we need to include the quantities sold at various prices */
,SUM(POQuantityForAverages)                                                                            AS TotalQuantities
,CAST(SUM(POQuantityForAverages * PurchasePrice)    AS DECIMAL(20, 2))                                 AS TotalPrice
,CAST(SUM(POQuantityForAverages * PurchasePrice) / SUM(POQuantityForAverages)    AS DECIMAL(20, 2))    AS AvgPrice
FROM
FindApplicableData
GROUP BY
ItemNumber
``````

As you can see, this no longer needs a cursor. This would allow you to create this as a view to make your subsequent querying much easier.

Hope this helps you.

• I think this would be an overall overage. Not a return average. Dec 16 '17 at 21:52
• You mean average? Yes...didnt interpret the calculation #3 correctly. Fixed code to be what the OP needs. Dec 17 '17 at 6:03