Блог

Первый запрос не выполняется, потому что у меня есть a SUM() внутри a LAG() , что просто незаконно, но я включил его просто для осмысления идеи.

 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
  

Или

 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
  

Теперь я должен упомянуть, что в «ready_set» есть два нулевых столбца, result и CumRes, поэтому второй запрос даже выполняется.

Вот точная идея, показанная в электронной таблице Excel. В принципе, это не очень сложно, но, я думаю, это в программировании! Формула Excel той же идеи

РЕДАКТИРОВАТЬ: результаты формулы в Excel

В то же время я пытаюсь сохранить текущую сумму результатов в CumRes и вычислить результаты на основе предыдущего кумулятивного значения. Они взаимозависимы, поэтому я не могу просто суммировать результаты, чтобы получить столбец CumRes. Я не совсем понимаю, как SQL генерирует строки, и у меня может быть совершенно неправильный подход, если я пытаюсь отстать от столбца, который находится в процессе создания, а не уже существующего. Вы видите мою проблему или ошибку в мышлении?

Похоже, это соответствует вашим формулам:

 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
  

См . Скрипку

 create table tableX (rownum int not null,
    ACC_NET_ int not null, CumACT int not null,
    FC_NET_AMOUNT int not null, CumFC int not null);

insert into tableX (rownum, ACC_NET_, CumACT, FC_NET_AMOUNT, CumFc) values
    (1, 40, 40, 50, 50), (2, 164, 204, 2050, 2100), (3, 1513, 1717, 0, 2100),
    (4, 1000, 2717, 500, 2600), (5, 100, 2817, 0, 2600);    
go

with iterated as (
    select rownum, ACC_NET_, CumACT, FC_NET_AMOUNT, CumFc,
        iif(CumFc > CumACT, FC_NET_AMOUNT, CumACT - 0) as result,
        0 as CumRes
    from tableX
    where rownum = 1
    union all
    select x.rownum, x.ACC_NET_, x.CumACT, x.FC_NET_AMOUNT, x.CumFc,
        f.result,
        f.result   anchor.CumRes
    from iterated as anchor inner join tableX x
            on x.rownum = anchor.rownum   1
        cross apply (
            select iif(x.CumFc > x.CumACT, x.FC_NET_AMOUNT, x.CumACT - anchor.CumRes)
        ) as f(result)
)
select * from iterated;
  

Рекурсивный запрос будет работать для ваших формул. Возможно, вам потребуется прочитать больше об этой теме, если проблема больше, чем указано в ваших данных выше. Также мне пришлось добавить rownum столбец, чтобы иметь возможность упорядочивать строки по порядку.

РЕДАКТИРОВАТЬ: я неправильно ввел одно из начальных значений, и это было исправлено.

https://rextester.com/UXXY92525

Если вам нужна совокупная сумма:

 select rs.*, sum(fc_net_amount) over (order by rownum)
from ready_set rs