Suma acumulada en la que los valores de las categorías se reemplazan/actualizan.

Estoy tratando de hacer funcionar una suma acumulada en la que los cambios de Cantidad de categorías/grupos sucedan en cualquier fecha determinada – cuando ocurre el cambio, el nuevo valor de esa categoría se convierte en parte de la suma acumulada, pero el valor anterior de esa categoría se ignora; por lo tanto, es una suma acumulada, pero solo basada en la más reciente de cada categoría (en ese punto en el tiempo).

Datos de ejemplo (SumAmount es el problema que se intenta resolver)

txnid | custid | trans_date | Category | amount | SumAmount

1 | 1 | 2020-01-01 | Ball | 5 | 5 – primera transacción, por lo que la suma es 5

2 | 1 | 2020-01-02 | Cup | 5 | 10 – la suma es 10 (Ball = 5, Cup = 5)

3 | 1 | 2020-01-03 | Ball | 2 | 7 – la suma es 7 (Ball = 2, Cup = 5)

4 | 1 | 2020-02-04 | Ball | 4 | 9 – la suma es 9 (Ball = 4, Cup = 5)

5 | 1 | 2020-02-05 | Ball | 1 | 6 – la suma es 6 (Ball = 1, Cup = 5)

6 | 1 | 2020-02-06 | Cup | 10 | 11 – la suma es 11 (Ball = 1, Cup = 10)

7 | 1 | 2020-02-07 | Phone | 5 | 16 – la suma es 16 (Ball = 1, Cup = 10, Phone = 5)

8 | 1 | 2020-02-08 | Cup | 5 | 11 – la suma es 11 (Ball = 1, Cup = 5, Phone = 5)

9 | 1 | 2020-02-09 | Ball | 5 | 15 – la suma es 15 (Ball = 5, Cup = 5, Phone = 5)

Tengo esto funcionando dentro de un cursor, pero me pregunto si es posible con bases SET.

El cursor funciona así:

CREATE PROCEDURE [dbo].[PriceHistory](@CustId int, @MaxPriceHistory decimal(16,2) Output)
create table #PrecioHistorico (CategoryID uniqueidentifier, Amount decimal(16,2))

declare pricehistory_cursor CURSOR FOR
select CategoryID, Amount
from mytable
where CustId = @CustId
order by trans_date;

declare @CategoryID uniqueidentifier
declare @Amount decimal(16,2)
declare @CurrentTotal decimal(16,2)

set @MaxPriceHistory = 0

open pricehistory_cursor
fetch next from pricehistory_cursor into @CategoryID, @Amount

WHILE @@FETCH_STATUS = 0
BEGIN
if (exists(select * from #PrecioHistorico where CategoryID = @CategoryID))
update #PrecioHistorico set Amount = @actualAmount where CategoryID = @CategoryID
else
insert into #PrecioHistorico(CategoryID,Amount) values (@CategoryID, @Amount)

select @CurrentTotal = sum(Amount) from #PrecioHistorico

if (@CurrentTotal > @MaxPriceHistory)
set @MaxPriceHistorico = @CurrentTotal

fetch next from pricehistory_cursor into @CategoryID, @Amount
END

close pricehistory_cursor
deallocate pricehistory_cursor;

En última instancia, estoy buscando la suma máxima (Max SumAmount) a lo largo de la vida de las transacciones (columna SumAmount en el ejemplo proporcionado), para este ejemplo sería 16.

Entiendo lo que hace el cursor y entiendo por qué funciona de esa manera (reemplazando Amount para esa cierta Categoría si ya existe (esto es lo que me confunde para un enfoque basado en SET, ¿cómo obtendría el monto de Cup de 5, cuando sucede txnid = 5?), y sumándolo junto con todos los otros montos de categoría más recientes hechos en ese punto en el tiempo), simplemente no puedo entender si es posible hacerlo con algún tipo de CTE recursiva o ROWNUMBER.