Is subselecting column values inside the table you're updating not Allowed?

Question

I'm making a stored procedure that computes the Moving Average Cost (MAC) of Each Spare parts in the Transactions Table. it is some sort of automatic checking and fixing of MAC of each row. I'm using Set-Based Approach because i'm avoiding cursor.

my transaction table has the following Columns:

Transaction No -- a record of the row's transaction Number TransacType -- transaction type value will be BEG (Beginning Balance), ISS (for Issuance) ,and RR (for Received Stocks)

Stock No -- Product Number

Cost -- the Cost per unit of the Stock Number

Quantity -- Quantity of the Transaction

MAC -- Moving Average Cost

Rules: - If the transaction is RR, the Sproc will compute the MAC. - the cost of all ISS transactions that will follow will be equal to the MAC of latest RR.

Sample Table

-----------------------------------------------------------------------
TransacNo     TransacType   StockNo Cost    Qty MAC 
-----------------------------------------------------------------------
0               BEG     AE1 450.00  10  450.00  
1               RR      AE1 460.00  05  453.33
2               ISS     AE1 453.33  01  ------
3               RR      AE1 460.00  05  455.09
4               ISS     AE1 455.09  01  ------
5               BEG     AE2 450.00  10  450.00
6               RR      AE2 460.00  05  453.33
7               ISS     AE2 453.33  01  ------
8               RR      AE2 460.00  05  455.09
9               ISS     AE2 455.09  01      ------
------------------------------------------------------------------------

My Formula for Moving Average Cost:

Moving Average Cost = ((Latest MAC * Current OnHand Qty) + (RR Cost * RR Qty))
            ---------------------------------------------------------------
                    (Current OnHand Qty + RR Qty)

My MAC Fixing Code:

Declare @LatestMAC decimal(18,2);
set @LatestMAC = 0.00

Declare @CurrentOnHand int;
Set @CurrentOnHand = 0


Declare @TotalISS int;
Declare @TotalRR int;

Update TransacTable
Set

@TotalISS =  
            ISNULL((
            Select SUM(Qty) 
            from 
                TransacTable TT 
            where 
                TT.TransacType = 'ISS' and
                TT.StockNo = StockNo and
                TT.TransacNo < TransacNo),0),   
@TotalRR =  
            ISNULL((
            Select SUM(Qty) 
            from 
                TransacTable TT 
            where 
                TT.TransacType = 'RR' and
                TT.StockNo = StockNo and
                TT.TransacNo < TransacNo),0),   

@LatestMAC = (Select top(1) ISNULL(MAC,0) 
            from 
                TransacTable TT 
            where 
                TT.TransacType = 'RR' and 
                TT.StockNo = StockNo and 
                TT.TransacNo < TransacNo 
            Order by 
                TT.TransacNo desc),

@CurrentOnHand = @TotalRR - @TotalISS,

MAC = 
Case
    WHEN TransacType = 'BEG'
        THEN MAC
    WHEN TransacType = 'RR'
        THEN ((@LatestMAC * @CurrentOnHand) + (Cost * Quantity) / (@CurrentOnHand * Quantity))
    else NULL
End,

Cost = 
Case
    When TransacType = 'ISS'
        THEN @LatestMAC
    else Cost
end

My Problem is The Required Varibles (TotalISS, TotalRR, CurrentMAC) to the MAC equation returns NULL.

Is selecting column values inside the table you're updating not Allowed? or there is an error in my solution itself? i derived my solution based on this example - Here. i don't want to use cursor so i used this approach.

Please Help me.

Answer 1

That looks over complicated.

Try

update yourtable
set
    mac =  ss  
from yourtable t1
    outer apply (
         Select 
              SUM(cost*quantity* case transactype when 'iss' then -1 else 1 end)
                  /SUM(quantity * case transactype when 'iss' then -1 else 1 end) as ss 
              from yourtable 
              where transacno <= t1.transacno 
              and stockno = t1.stockno
              ) t2