LOOP Statement help

[rose4567]

Hello All,
I am trying to write a loop statement. It needs to reach each row in the result set and keep subtracting qty from qty_on_hand until a negative number is reached.
The goal is to multiply qty by unit_cost for each row, but only until qty equals qty_on_hand. This means that sometimes I will only need to calculate part of the total qty to reach the qty_on_hand number.
I have the loop statement partially written, but cannot figure out how to finish it off.
Here is my code and some samples to help illustrate what I'm trying to accomplish. If I need two separate statements or if I'm going about this all wrong, please let me know. Any advice or help is greatly appreciated!

I'm looking to accomplish this:

73 is the number from field qty_on_hand that is the starting number and is the number I am comparing qty against.

loop qty calculation unit cost dif
1 12 73-12=61 2.18 26.16
2 20 61-20=41 2.78 55.60
3 10 41-10=31 2.78 27.80
4 10 31-10=21 2.78 27.80
5 20 21-20=1 2.78 55.60
6 10 1-10=(9)+10=1 2.78 2.78
END LOOP WHEN NEGATIVE REACHED AND ADD BACK QTY TO OBTAIN DIFFERENCE. MULTIPLY DIFFERENCE BY UNIT_COST.

start value in field "qty_on_hand" = 60 and that is static. The first row


DECLARE value_Cursor CURSOR FOR
SELECT tran_ts, sum(quantity - coalesce(receipt_qty_issued,0)) as qty,
COALESCE((SELECT QTY_ON_HAND
FROM STOCK_ITEM_LOCATIONS
WHERE (STOCK_ITEM_LOCATIONS.SITE_ID = IV_TRANSACTIONS.SITE_ID) AND
(STOCK_ITEM_LOCATIONS.STOCK_ITEM_NBR = IV_TRANSACTIONS.STOCK_ITEM_NBR)
GROUP BY STOCK_ITEM_LOCATIONS.SITE_ID, STOCK_ITEM_LOCATIONS.STOCK_ITEM_NBR, STOCK_ITEM_LOCATIONS.QTY_ON_HAND), 0) AS qty_on_hand,
sum(unit_cost) as unit_cost, sum((quantity - coalesce(receipt_qty_issued,0)) * unit_cost) as dif
FROM IV_TRANSACTIONS
WHERE IV_TRANSACTIONS.iv_type = 'RCP'
and IV_TRANSACTIONS.site_id = 'ADMN'
and IV_TRANSACTIONS.stock_item_nbr = '100'
GROUP BY TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR;
OPEN value_Cursor;
FETCH NEXT FROM value_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM value_Cursor;
END;
CLOSE value_Cursor;
DEALLOCATE value_Cursor;
GO

 

[rose4567]

I found the solution. I don't know how to close out this thread or remove it, so as a resolution, I am posting the code in case anyone else needs to do something like this. The stored procedure runs on two parameters that make a unique result set - in my case, site_id and stock_item_nbr.

________________________________

DECLARE curRunningTotal CURSOR LOCAL FAST_FORWARD FOR

SELECT IV_TRANSACTIONS.TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR, COALESCE (SUM(IV_TRANSACTIONS.UNIT_COST), 0)
AS unit_cost,
SUM(IV_TRANSACTIONS.QUANTITY - COALESCE (IV_TRANSACTIONS.RECEIPT_QTY_ISSUED, 0)) AS qty,
COALESCE((SELECT SUM(QTY_ON_HAND) AS Expr1
FROM STOCK_ITEM_LOCATIONS
WHERE (SITE_ID = iv_transactions.SITE_ID) AND (STOCK_ITEM_NBR = iv_transactions.STOCK_ITEM_NBR)
GROUP BY SITE_ID, STOCK_ITEM_NBR), 0) AS qty_on_hand

FROM IV_TRANSACTIONS
WHERE IV_TRANSACTIONS.SITE_ID = @site and IV_TRANSACTIONS.STOCK_ITEM_NBR = @stock
GROUP BY IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR, IV_TRANSACTIONS.TRAN_TS
ORDER BY IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR, IV_TRANSACTIONS.TRAN_TS desc;



OPEN curRunningTotal

DECLARE @tran_ts DATETIME

DECLARE @site_id CHAR(5)

DECLARE @stock_item_nbr CHAR(20)

DECLARE @unit_cost MONEY

DECLARE @qty INT

DECLARE @qty_on_hand INT

DECLARE @RunningTotal INT

DECLARE @TotalDollar MONEY

SET @RunningTotal = 0

SET @TotalDollar = 0



DECLARE @Report TABLE(

tran_ts DATETIME NOT NULL PRIMARY KEY,

site_id CHAR(5),

stock_item_nbr CHAR(20),

unit_cost MONEY,

qty INT,

qty_on_hand INT,

RunningTotal INT,

TotalDollar MONEY

)

FETCH NEXT FROM curRunningTotal

INTO @tran_ts,

@site_id,

@stock_item_nbr,

@unit_cost,

@qty,

@qty_on_hand



WHILE @@FETCH_STATUS = 0 and @RunningTotal <= @qty_on_hand

BEGIN

SET @RunningTotal = @RunningTotal + @qty

IF @RunningTotal >= @qty_on_hand
SET @TotalDollar = @TotalDollar + ((@qty - (@RunningTotal - @qty_on_hand)) * @unit_cost)
ELSE
SET @TotalDollar = @TotalDollar + (@qty * @unit_cost)


INSERT @Report

VALUES(@tran_ts, @site_id, @stock_item_nbr, @unit_cost, @qty, @qty_on_hand, @RunningTotal, @TotalDollar)

FETCH NEXT FROM curRunningTotal

INTO @tran_ts,
@site_id,
@stock_item_nbr,
@unit_cost,
@qty,
@qty_on_hand

END

CLOSE curRunningTotal

DEALLOCATE curRunningTotal


SELECT *

FROM @Report

ORDER BY site_id, stock_item_nbr, tran_ts desc