Calculating Run Cost for lengths of Pipe & Pile

Question

I work for a small company and we're trying to get away from Excel workbooks for Inventory control. I thought I had it figured out with help from (Nasser) but its beyond me. This is what I can get into a table, from there I need too get it to look like the table below.

My data

ID|GrpID|InOut| LoadFt  | LoadCostft| LoadCost |    RunFt  | RunCost| AvgRunCostFt  
1    1     1    4549.00       0.99       4503.51    4549.00     0        0  
2    1     1    1523.22       1.29     1964.9538    6072.22     0        0  
3    1     2   -2491.73          0             0    3580.49     0        0  
4    1     2     -96.00          0             0    3484.49     0        0  
5    1     1    8471.68       1.41    11945.0688    11956.17    0        0  
6    1     2    -369.00          0             0    11468.0568  0        0  
7    2     1    1030.89       5.07       5223.56    1030.89     0        0  
8    2     1     314.17       5.75     1806.4775    1345.06     0        0  
9    2     1     239.56        6.3       1508.24    1509.228    0        0  
10   2     2    -554.46          0             0    954.768     0        0  
11   2     1     826.24      5.884     4861.5961    1781.008    0        0

Expected output

ID|GrpID|InOut| LoadFt  | LoadCostft| LoadCost |    RunFt  | RunCost| AvgRunCostFt  
1    1     1    4549.00       0.99       4503.51    4549.00     4503.51     0.99   
2    1     1    1523.22       1.29     1964.9538    6072.22    6468.4638  1.0653  
3    1     2   -2491.73     1.0653    -2490.6647    3580.49    3977.7991   1.111  
4    1     2     -96.00      1.111      -106.656    3484.49     3871.1431  1.111  
5    1     1    8471.68       1.41    11945.0688    11956.17   15816.2119 1.3228  
6    1     2    -369.00     1.3228     -488.1132    11468.0568 15328.0987 1.3366  
7    2     1    1030.89       5.07       5223.56    1030.89     5223.56    5.067  
8    2     1     314.17       5.75     1806.4775    1345.06    7030.0375  5.2266  
9    2     1     239.56        6.3       1508.24    1509.228   8539.2655   5.658  
10   2     2    -554.46      5.658    -3137.1346    954.768    5402.1309   5.658  
11   2     1     826.24      5.884     4861.5961    1781.008   10263.727  5.7629  

The first record of a group would be considered the opening balance. Inventory going into the yard have the ID of 1 and out of the yard are 2's. Load footage going into the yard always has a load cost per foot and I can calculate the the running total of footage. The first record of a group is easy to calculate the run cost and run cost per foot. The next record becomes a little more difficult to calculate. I need to move the average of run cost per foot forward to the load cost per foot when something is going out of the yard and then calculate the run cost and average run cost per foot again. Hopefully this makes sense to somebody and we can automate some of these calculations. Thanks for any help.

Here's an Oracle example I found;

SQL> select order_id
2       , volume
3       , price
4       , total_vol
5       , total_costs
6       , unit_costs
7    from ( select order_id
8                , volume
9                , price
10                , volume total_vol
11                , 0.0 total_costs
12                , 0.0 unit_costs
13                , row_number() over (order by order_id) rn
14             from costs
15            order by order_id
16         )
17   model
18         dimension by (order_id)
19         measures (volume, price, total_vol, total_costs, unit_costs)
20         rules iterate (4)
21         ( total_vol[any] = volume[cv()] + nvl(total_vol[cv()-1],0.0)
22         , total_costs[any]
23           = case SIGN(volume[cv()])
24             when -1 then total_vol[cv()] * nvl(unit_costs[cv()-1],0.0)
25             else volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0.0)
26             end
27         , unit_costs[any] = total_costs[cv()] / total_vol[cv()]
28         )
29   order by order_id
30  /

ORDER_ID     VOLUME      PRICE  TOTAL_VOL TOTAL_COSTS UNIT_COSTS
---------- ---------- ---------- ---------- ----------- ----------
     1       1000        100       1000      100000        100
     2       -500        110        500       50000        100
     3       1500         80       2000      170000         85
     4       -100        150       1900      161500         85
     5       -600        110       1300      110500         85
     6        700        105       2000      184000         92

6 rows selected.

Answer 1

Let me say first off three things:

• This is certainly not the best way to do it. There is a rule saying that if you need a while-loop, then you are most probably doing something wrong.
• I suspect there is some calculation errors in your original "Expected output", please check the calculations since my calculated values are different according to your formulas.
• This question could also be seen as a gimme teh codez type of question, but since you asked a decently formed question with some follow-up research, my answer is below. (So no upvoting since this is help for a specific case)

Now onto the solution:

I attempted to use my initial hint of the LAG statement in a nicely formed single update statement, but since you can only use a windowed function (aka LAG) inside a select or order by clause, that will not work.

What the code below does in short:
It calculates the various calculated fields for each record when they can be calculated and with the appropriate functions, updates the table and then moves onto the next record.
Please see comments in the code for additional information.
TempTable is a demo table (visible in the linked SQLFiddle).
Please read this answer for information about decimal(19, 4)

-- Our state and running variables
DECLARE @curId       INT = 0,
        @curGrpId    INT,
        @prevId      INT = 0,
        @prevGrpId   INT = 0,
        @LoadCostFt  DECIMAL(19, 4),
        @RunFt       DECIMAL(19, 4),
        @RunCost     DECIMAL(19, 4)

WHILE EXISTS (SELECT 1
                FROM TempTable
               WHERE DoneFlag = 0) -- DoneFlag is a bit column I added  to the table for calculation purposes, could also be called "IsCalced"
BEGIN
  SELECT top 1  -- top 1 here to get the next row based on the ID column
         @prevId   = @curId,
         @curId    = tmp.ID,
         @curGrpId = Grpid
    FROM TempTable tmp
   WHERE tmp.DoneFlag = 0
   ORDER BY tmp.GrpID, tmp.ID -- order by to ensure that we get everything from one GrpID first

   -- Calculate the LoadCostFt.
   -- It is either predetermined (if InOut = 1) or derived from the previous record's AvgRunCostFt (if InOut = 2)
   SELECT @LoadCostFt = CASE
                          WHEN tmp.INOUT = 2
                            THEN (lag(tmp.AvgRunCostFt, 1, 0.0) OVER (partition BY GrpId ORDER BY ID))
                          ELSE tmp.LoadCostFt
                        END
     FROM TempTable tmp
    WHERE tmp.ID IN (@curId, @prevId)
      AND tmp.GrpID = @curGrpId

   -- Calculate the LoadCost
   UPDATE TempTable
      SET LoadCost = LoadFt * @LoadCostFt
    WHERE Id = @curId

   -- Calculate the current RunFt and RunCost based on the current LoadFt and LoadCost plus the previous row's RunFt and RunCost
   SELECT @RunFt = (LoadFt + (lag(RunFt, 1, 0) OVER (partition BY GrpId ORDER BY ID))),
          @RunCost = (LoadCost + (lag(RunCost, 1, 0) OVER (partition BY GrpId ORDER BY ID)))
     FROM TempTable tmp
    WHERE tmp.ID IN (@curId, @prevId)
      AND tmp.GrpID = @curGrpId

   -- Set all our values, including the AvgRunCostFt calc
   UPDATE TempTable
      SET RunFt        = @RunFt,
          RunCost      = @RunCost,
          LoadCostFt   = @LoadCostFt,
          AvgRunCostFt = @RunCost / @RunFt,
          doneflag     = 1
    WHERE ID = @curId
END

SELECT ID, GrpID, InOut, LoadFt, RunFt, LoadCost,
       RunCost, LoadCostFt, AvgRunCostFt
  FROM TempTable
 ORDER BY GrpID, Id

The output with your sample data and a SQLFiddle demonstrating how it all works:

ID  GrpID  InOut  LoadFt   RunFt     LoadCost      RunCost     LoadCostFt  AvgRunCostFt
1   1      1      4549     4549      4503.51       4503.51     0.99        0.99
2   1      1      1523.22  6072.22   1964.9538     6468.4638   1.29        1.0653
3   1      2      -2491.73 3580.49   -2654.44      3814.0238   1.0653      1.0652
4   1      2      -96      3484.49   -102.2592     3711.7646   1.0652      1.0652
5   1      1      8471.68  11956.17   11945.0688   15656.8334  1.41        1.3095
6   1      2      -369     11587.17  -483.2055     15173.6279  1.3095      1.3095
7   2      1      1030.89  1030.89   5226.6123     5226.6123   5.07        5.07
8   2      1      314.17   1345.06   1806.4775     7033.0898   5.75        5.2288
9   2      1      239.56   1584.62   1509.228      8542.3178   6.3         5.3908
10  2      2      -554.46  1030.16   -2988.983     5553.3348   5.3908      5.3907
11  2      1      826.24   1856.4    4861.5962     10414.931   5.884       5.6103

If you are unclear about parts of the code, I can update with additional explanations.