title: Can this Recursive Solution be written up into a T-SQL Query using CTE or OVER?
body: <p>Lets imagine you have the following table called Table1 of Orders in chronological order returned from an In-line UDF. Please note that the OrderID may be out of sync so I have intentionally created an anomaly there (i.e. I have not included the Date field but I have access to the column if easier for you).</p>
  
  <pre><code>   OrderID  BuySell  FilledSize  ExecutionPrice  RunningTotal AverageBookCost  RealisedPnL
     339      Buy      2           24.5            NULL         NULL             NULL
     375      Sell     3           23.5            NULL         NULL             NULL
     396      Sell     3           20.5            NULL         NULL             NULL
     416      Sell     1           16.4            NULL         NULL             NULL
     405      Buy      4           18.2            NULL         NULL             NULL
     421      Sell     1           16.7            NULL         NULL             NULL
     432      Buy      3           18.6            NULL         NULL             NULL
  </code></pre>
  
  <p>I have a function that I would like to apply recursively from the top to the bottom that will calculate the 3 NULL columns, however the imputs into the function will be the outputs from the previous call. The function I have created is called mfCalc_RunningTotalBookCostPnL and I have attached this below</p>
  
  <pre><code>CREATE FUNCTION [fMath].[mfCalc_RunningTotalBookCostPnL](
      @BuySell           VARCHAR(4),
      @FilledSize        DECIMAL(31,15),
      @ExecutionPrice    DECIMAL(31,15),
      @OldRunningTotal   DECIMAL(31,15),
      @OldBookCost       DECIMAL(31,15)
      )
  
  RETURNS @ReturnTable TABLE(
      NewRunningTotal DECIMAL(31,15),
      NewBookCost DECIMAL(31,15),
      PreMultRealisedPnL  DECIMAL(31,15)
      )
  AS
  BEGIN
      DECLARE @SignedFilledSize   DECIMAL(31,15),
              @NewRunningTotal    DECIMAL(31,15),
              @NewBookCost        DECIMAL(31,15),
              @PreMultRealisedPnL DECIMAL(31,15)
  
      SET @SignedFilledSize = fMath.sfSignedSize(@BuySell, @FilledSize)
      SET @NewRunningTotal = @OldRunningTotal + @SignedFilledSize
      SET @PreMultRealisedPnL = 0
      IF SIGN(@SignedFilledSize) = SIGN(@OldRunningTotal)
          -- This Trade is adding to the existing position.
          SET @NewBookCost = (@SignedFilledSize * @ExecutionPrice +
              @OldRunningTotal * @OldBookCost) / (@NewRunningTotal)
      ELSE
      BEGIN
          -- This trade is reversing the existing position.
          -- This could be buying when short or selling when long.
          DECLARE @AbsClosedSize DECIMAL(31,15)
          SET @AbsClosedSize = fMath.sfMin(ABS(@SignedFilledSize), ABS(@OldRunningTotal));
  
          -- There must be Crystalising of PnL.
          SET @PreMultRealisedPnL = (@ExecutionPrice - @OldBookCost) * @AbsClosedSize * SIGN(-@SignedFilledSize)
  
          -- Work out the NewBookCost
          SET @NewBookCost = CASE
              WHEN ABS(@SignedFilledSize) &lt; ABS(@OldRunningTotal) THEN @OldBookCost
              WHEN ABS(@SignedFilledSize) = ABS(@OldRunningTotal) THEN 0
              WHEN ABS(@SignedFilledSize) &gt; ABS(@OldRunningTotal) THEN @ExecutionPrice
          END
      END
  
      -- Insert values into Return Table
      INSERT INTO @ReturnTable
          VALUES (@NewRunningTotal, @NewBookCost, @PreMultRealisedPnL)
  
      -- Return
      RETURN
  END
  </code></pre>
  
  <p>So the t-SQL command I am looking for (I dont mind if someone can creates an Outer Apply too) would generate the following Result/Solution set:</p>
  
  <pre><code>OrderID BuySell FilledSize ExecutionPrice RunningTotal AverageBookCost RealisedPnL
  339     Buy     2          24.5           2            24.5            0
  375     Sell    3          23.5           -1           23.5            -2
  396     Sell    3          20.5           -4           21.25           0
  416     Sell    1          16.4           -5           20.28           0
  405     Buy     4          18.2           -1           20.28           8.32
  421     Sell    1          16.7           -2           18.49           0
  432     Buy     3          18.6           1            18.6            -0.29
  </code></pre>
  
  <p>A few notes, the above stored procedure calls a trivial function fMath.sfSignedSize which just makes ('Sell',3) = -3. Also, for the avoidance of doubt, I would see the solution making these calls in this order assuming I am correct in my calculations! (Note that I start off assuming the OldRunningTotal and OldBookCost are both zero):</p>
  
  <pre><code>SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',2,24.5,0,0)
  SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',3,23.5,2,24.5)
  SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',3,20.5,-1,23.5)
  SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',1,16.4,-4,21.25)
  SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',4,18.2,-5,20.28)
  SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',1,16.7,-1,20.28)
  SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',3,18.6,-2,18.49)
  </code></pre>
  
  <p>Obviously, the [fMath].[mfCalc_RunningTotalBookCostPnL] may need to be tweaked so that it can start off with NULL entries as the OldRunningTotal and OldBookCost but this is trivially done. The SQL Set theory of applying the resursive nature is a little harder.</p>
  
  <p>Many thanks,
  Bertie.</p>