2 Reread the calculation for average price and corrected code to match it. edited Dec 17 '17 at 6:02 Kent Chenery 53122 silver badges77 bronze badges ``````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 averagesaverage */ SELECT price - its not a ItemNumber simple AVG as we ,AVG(PurchasePrice)need to include the quantities sold at various ASprices AvgPurchasePrice*/ ,COUNTSUM(*POQuantityForAverages) AS RecordsMatched /* You may need these averages though? */ ,CAST(SUM(POQuantity) AS NUMERIC(20, 2)) AS TotalQuantityTotalQuantities ,CAST(SUM(POQuantityPOQuantityForAverages * PurchasePrice) AS NUMERICDECIMAL(20, 2)) AS TotalPurchasePriceTotalPrice ,CAST(SUM(POQuantityPOQuantityForAverages * PurchasePrice) / SUM(POQuantityPOQuantityForAverages) AS NUMERICDECIMAL(20, 2)) AS AveragePurchasePriceWithQuantitiesConsideredAvgPrice FROM FindApplicableData GROUP BY ItemNumber `````` ``````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 * ,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 * ,RunningTotalQuantity - POQuantity AS PreviousRunningTotalQuantity FROM CalculateRunningTotals WHERE ABS(ReturnQuantity) >= (RunningTotalQuantity - POQuantity) /* Compare to previous running total - remove this to see why you need it ;-) */ ) /* Calculate the averages */ SELECT ItemNumber ,AVG(PurchasePrice) AS AvgPurchasePrice ,COUNT(*) AS RecordsMatched /* You may need these averages though? */ ,CAST(SUM(POQuantity) AS NUMERIC(20, 2)) AS TotalQuantity ,CAST(SUM(POQuantity * PurchasePrice) AS NUMERIC(20, 2)) AS TotalPurchasePrice ,CAST(SUM(POQuantity * PurchasePrice) / SUM(POQuantity) AS NUMERIC(20, 2)) AS AveragePurchasePriceWithQuantitiesConsidered FROM FindApplicableData GROUP BY ItemNumber `````` ``````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 `````` 1 answered Dec 16 '17 at 21:03 Kent Chenery 53122 silver badges77 bronze badges 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 * ,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 * ,RunningTotalQuantity - POQuantity AS PreviousRunningTotalQuantity FROM CalculateRunningTotals WHERE ABS(ReturnQuantity) >= (RunningTotalQuantity - POQuantity) /* Compare to previous running total - remove this to see why you need it ;-) */ ) /* Calculate the averages */ SELECT ItemNumber ,AVG(PurchasePrice) AS AvgPurchasePrice ,COUNT(*) AS RecordsMatched /* You may need these averages though? */ ,CAST(SUM(POQuantity) AS NUMERIC(20, 2)) AS TotalQuantity ,CAST(SUM(POQuantity * PurchasePrice) AS NUMERIC(20, 2)) AS TotalPurchasePrice ,CAST(SUM(POQuantity * PurchasePrice) / SUM(POQuantity) AS NUMERIC(20, 2)) AS AveragePurchasePriceWithQuantitiesConsidered 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.