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.