Recursivly call on a lagged value in the same column, Or Have two interdependent columns

The first query does not run because I have a SUM() inside a LAG() which is just illegal but I’ve included it just to conceptualize the idea.

SELECT
     GUID_Key
    ,AFE_Number
    ,UWI
    ,Date
    ,ACT_NET_AMOUNT
    ,Cum_Act
    ,FC_NET_AMOUNT
    ,Cum_FC
    ,ROWNUM
    ,IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, Cum_Act-ISNULL(LAG(SUM(Cum_Act)OVER(PARTITION BY Date))OVER (ORDER BY ROWNUM),0)) AS Result

FROM ready_set

Or

SELECT
     GUID_Key
    ,AFE_Number
    ,UWI
    ,Date
    ,ACT_NET_AMOUNT
    ,Cum_Act
    ,FC_NET_AMOUNT
    ,Cum_FC
    ,ROWNUM
    ,IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL, Cum_Act, Cum_Act - LAG(CumRes) OVER( ORDER BY ROWNUM))) AS Result
    ,IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL ,/*Result*/IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL, Cum_Act, Cum_Act - LAG(CumRes) OVER( ORDER BY ROWNUM))), /*Result*/(IIF(Cum_FC > Cum_Act, FC_NET_AMOUNT, IIF((LAG(CumRes) OVER( ORDER BY ROWNUM)) IS NULL, Cum_Act, Cum_Act - LAG(CumRes) OVER( ORDER BY ROWNUM))) + Lag(CumRes) OVER ( ORDER BY ROWNUM))) AS CumRes
FROM ready_set

Now I should mention there is are two null columns, result and CumRes, in “ready_set” which is why the second query even runs.

Here is the exact idea shown in an Excel spreadsheet. It is not extremely complex in principle but it is in programming I guess! Excel Formula of the same idea

EDIT: Excel results of the formula

I am trying to, at the same time, keep a running total of the results under CumRes, and calculate Results based off the previous cumulative. They are interdependent so I can’t simply sum Results to get the CumRes column. I’m not completely aware on how SQL generates rows and I may have the completely wrong approach if I’m trying to Lag on a column that is in the process of being made instead of pre existing. Do you see my problem or error in thinking?

Answer

This seems to match your formulas:

with cte as 
 (
   select ACC_NET_AMOUNT, fc_NET_AMOUNT
     -- cumulative sums up to the previous rows
     , coalesce(sum(ACC_NET_AMOUNT) over (order by rownum rows between unbounded preceding and 1 preceding) ,0) as cumacc
     , coalesce(sum( FC_NET_AMOUNT) over (order by rownum rows between unbounded preceding and 1 preceding), 0) as cumfc
   from tableX
 )
select ACC_NET_AMOUNT, fc_NET_AMOUNT
  , ca.CumResult - case when cumfc > cumacc then cumfc else cumacc end as Result
  , ca.CumResult
from cte 
cross apply
 ( select
      case when cumfc+FC_NET_AMOUNT > cumacc + ACC_NET_AMOUNT 
           then cumfc+FC_NET_AMOUNT 
           else cumacc+ACC_NET_AMOUNT 
      end as CumResult) as ca

See fiddle