Fork me on GitHub

Anyone happen to know how to solve a SQL query where the value of one line depends on an aggregate calculated for the previous line?


@rovanion That requires a recursive cte. Here is an example in TSQL:

declare @transactions as table (
    id integer primary key identity(1,1),
    feed_g integer

insert into @transactions 
values (50), (50), (50), (50);

with indexed_transactions as (
    select *, row_number() over (order by id) as rn
    from @transactions
cte as (
    select cast(0 as bigint) as rn, 0 as id, 0 as feed_g, cast(0.0 as float) as row_weighted_sum

    union all
        case when cte.row_weighted_sum + a.feed_g > 75 then cte.row_weighted_sum + a.feed_g
        else cte.row_weighted_sum + a.feed_g * 0.5 end as row_weighted_sum
    from indexed_transactions a
    join cte on cte.rn = a.rn - 1
select * from cte where id > 0


Thank you so incredibly much! I've been thinking about this issue on and off for two months, seemingly inching closer but never quite getting there. I'll have a look at this tomorrow and see if I can wrap my head around your code.