SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #TEMP( PlanID NVARCHAR(10),EEID NVARCHAR(15),Ticker NVARCHAR(10)
    ,MarketValue MONEY,TotalBalance MONEY,CurrentDenominator MONEY,AllocationAmount DECIMAL(18,6)
    ,CheckValue float(53) );
INSERT INTO #TEMP VALUES
('10241299',    '70973',    'VEMAX',    5654.12,    241781.86,  241781.86,  1,0.0233),
('10241299',    '70973',    'VMGMX',    5972.14,    241781.86,  236127.74,  1,0.02461284),
('10241299',    '70973',    'VTMGX',    35099.02,   241781.86,  230155.6,   1,0.1451932919),
('10241299',    '70973',    'VTSAX',    45529.07,   241781.86,  195056.58,  1,0.18832902881454),
('10241299',    '70973',    'VFIAX',    149527.51,  241781.86,  149527.51,  1,0.61856483928546)
  ;
SELECT PlanID ,EEID ,TotalBalance ,Ticker ,MarketValue,CurrentDenominator 
    ,AllocationAmount,IncrementedSourceRatio = (MarketValue/CurrentDenominator),CheckValue
INTO #TEMP2
FROM #TEMP;
SELECT PlanID ,EEID ,TotalBalance ,Ticker,MarketValue 
    ,CurrentDenominator ,AllocationAmount ,IncrementedSourceRatio,CheckValue
FROM #TEMP2


PlanID
EEID
TotalBalance
Ticker
MarketValue
CurrentDenominator
AllocationAmount
IncrementedSourceRatio
CheckValue


10241299
70973
241781.8600
VEMAX
5654.1200
241781.8600
1.000000
0.0233
0.0233

10241299
70973
241781.8600
VMGMX
5972.1400
236127.7400
1.000000
0.0252
0.02461284

10241299
70973
241781.8600
VTMGX
35099.0200
230155.6000
1.000000
0.1525
0.1451932919

10241299
70973
241781.8600
VTSAX
45529.0700
195056.5800
1.000000
0.2334
0.18832902881454

10241299
70973
241781.8600
VFIAX
149527.5100
149527.5100
1.000000
1.0000
0.61856483928546


select row_number()over(order by PlanID, EEID, MarketValue, Ticker)rn
  ,AllocationAmount G, IncrementedSourceRatio H
 ,CheckValue J
from #temp2


rn
G
H
J


1
1.000000
0.0233
0.0233

2
1.000000
0.0252
0.02461284

3
1.000000
0.1525
0.1451932919

4
1.000000
0.2334
0.18832902881454

5
1.000000
1.0000
0.61856483928546


-- with float(53)
with t as(select row_number()over(order by PlanID, EEID, MarketValue, Ticker)rn
  ,cast(AllocationAmount as float(53)) G, cast(IncrementedSourceRatio as float(53)) H
 ,CheckValue T
from #temp2
)
,r as(
  select  rn,G,H,H*G as I
     ,H*G as s
     ,T
  from t where rn=1
  union all
  select  t.rn,t.G,t.H,t.H*(t.G-r.s) as I
     ,t.H*(t.G-r.s) +r.s as s
     ,t.T
  from r inner join t on t.rn=(r.rn+1)
)
select * from r;


rn
G
H
I
s
T


1
1
0.0233
0.0233
0.0233
0.0233

2
1
0.0252
0.02461284
0.04791284
0.02461284

3
1
0.1525
0.1451932919
0.1931061319
0.1451932919

4
1
0.2334
0.18832902881454
0.38143516071454
0.18832902881454

5
1
1
0.61856483928546
1
0.61856483928546


-- with decimal (38,6)
with t as(select row_number()over(order by PlanID, EEID, MarketValue, Ticker)rn
  ,AllocationAmount G, IncrementedSourceRatio H
 ,CheckValue T
from #temp2
)
,r as(
  select  rn,G,H,cast(H*G as decimal(38,6)) as I
     ,cast(H*G as decimal(38,6)) as s
     ,T
  from t where rn=1
  union all
  select  t.rn,t.G,t.H,cast(t.H*(t.G-r.s) as decimal(38,6)) as I
     ,cast(t.H*(t.G-r.s) as decimal(38,6))+r.s as s
     ,t.T
  from r inner join t on t.rn=(r.rn+1)
)
select * from r;


rn
G
H
I
s
T


1
1.000000
0.0233
0.023300
0.023300
0.0233

2
1.000000
0.0252
0.024613
0.047913
0.02461284

3
1.000000
0.1525
0.145193
0.193106
0.1451932919

4
1.000000
0.2334
0.188329
0.381435
0.18832902881454

5
1.000000
1.0000
0.618565
1.000000
0.61856483928546


with sum_cte as (
    select *
           ,IncrementedSourceRatio = (MarketValue/CurrentDenominator)
           ,sum(MarketValue/CurrentDenominator) over (order by PlanID, EEID, MarketValue, Ticker) cum_isr
    from #temp)
select *
    ,lag(cum_isr, 1, 0) over (order by PlanID, EEID, MarketValue, Ticker) lag_cum_isr
    ,IncrementedSourceRatio*(AllocationAmount-lag(cum_isr, 1, 0) over (order by PlanID, EEID, MarketValue, Ticker)) final_answer
FROM sum_cte;


PlanID
EEID
Ticker
MarketValue
TotalBalance
CurrentDenominator
AllocationAmount
CheckValue
IncrementedSourceRatio
cum_isr
lag_cum_isr
final_answer


10241299
70973
VEMAX
5654.1200
241781.8600
241781.8600
1.000000
0.0233
0.0233
0.0233
0.0000
0.023300

10241299
70973
VMGMX
5972.1400
241781.8600
236127.7400
1.000000
0.02461284
0.0252
0.0485
0.0233
0.024613

10241299
70973
VTMGX
35099.0200
241781.8600
230155.6000
1.000000
0.1451932919
0.1525
0.2010
0.0485
0.145104

10241299
70973
VTSAX
45529.0700
241781.8600
195056.5800
1.000000
0.18832902881454
0.2334
0.4344
0.2010
0.186487

10241299
70973
VFIAX
149527.5100
241781.8600
149527.5100
1.000000
0.61856483928546
1.0000
1.4344
0.4344
0.565600

PlanID	EEID	TotalBalance	Ticker	MarketValue	CurrentDenominator	AllocationAmount	IncrementedSourceRatio	CheckValue
10241299	70973	241781.8600	VEMAX	5654.1200	241781.8600	1.000000	0.0233	0.0233
10241299	70973	241781.8600	VMGMX	5972.1400	236127.7400	1.000000	0.0252	0.02461284
10241299	70973	241781.8600	VTMGX	35099.0200	230155.6000	1.000000	0.1525	0.1451932919
10241299	70973	241781.8600	VTSAX	45529.0700	195056.5800	1.000000	0.2334	0.18832902881454
10241299	70973	241781.8600	VFIAX	149527.5100	149527.5100	1.000000	1.0000	0.61856483928546

rn	G	H	I	s	T
1	1.000000	0.0233	0.023300	0.023300	0.0233
2	1.000000	0.0252	0.024613	0.047913	0.02461284
3	1.000000	0.1525	0.145193	0.193106	0.1451932919
4	1.000000	0.2334	0.188329	0.381435	0.18832902881454
5	1.000000	1.0000	0.618565	1.000000	0.61856483928546