patternsqlMinor

Iterating through a record set and selecting certain records

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

iteratingrecordsrecordselectingthroughandcertainset

Problem

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

Solution

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:

https://www.brentozar.com/sql-syntax-examples/window-function-examples-sql-server/

https://www.red-gate.com/simple-talk/sql/learn-sql-server/window-functions-in-sql-server/

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

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 = (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.

Code Snippets

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

Context

StackExchange Database Administrators Q#193258, answer score: 3

Revisions (0)

No revisions yet.