What needs to happen: create a new column called AdjustedAllocation by taking the IncrementedSourceRatio and multiplying it by the original AllocationAmount LESS the SUM of all previous AdjustedAllocation for that participant.
Issue is that I'm referencing the value I'm trying to calculate in the same column. And I need all the prior calculated values to make the correct adjustment. The last column is the expected value.
| (A) PlanID | (B) EEID | (C) TotalBalance | (D) Ticker | (E) MarketValue | (F) CurrentDenominator | (G) AllocationAmount | (H) IncrementedSourceRatio | (I) Adjusted Allocation Formula | (J) Adjusted Allocation |
|---|---|---|---|---|---|---|---|---|---|
| 10241299 | 11973 | 241781.86 | VEMAX | 5654.12 | 241781.86 | 1 | 0.0233 | =H2*G2 | 0.0233 |
| 10241299 | 11973 | 241781.86 | VMGMX | 5972.14 | 236127.74 | 1 | 0.0252 | =H3*(G3-J2) | 0.02461284 |
| 10241299 | 11973 | 241781.86 | VTMGX | 35099.02 | 230155.6 | 1 | 0.1525 | =H4*(G4-(J2+J3)) | 0.1451932919 |
| 10241299 | 11973 | 241781.86 | VTSAX | 45529.07 | 195056.58 | 1 | 0.2334 | =H5*(G5-(J2+J3+J4)) | 0.18832902881454 |
| 10241299 | 11973 | 241781.86 | VFIAX | 149527.51 | 149527.51 | 1 | 1 | =H6*(G6-(J2+J3+J4+J5)) | 0.61856483928546 |
I've tried variations of LAG(), SUM(), COALESCE(), adding ROW_NUMBER(), and all kinds of temp tables to no avail.
I've narrowed it down to some kind of recursive query/CTE, but most of the examples I have found are very simple and don't exactly fit what I'm trying to do. I'm still trying to learn how to apply CTEs and I'm hoping seeing a solution for this item will help solidify my understanding.
These solutions came close, but not exactly what I need to accomplish:
Thank you in advance for helping my learning.
IF OBJECT_ID('tempdb..#TEMP', 'u') IS NOT NULL DROP TABLE #TEMP
IF OBJECT_ID('tempdb..#TEMP2', 'u') IS NOT NULL DROP TABLE #TEMP2
GO
CREATE TABLE #TEMP
(
PlanID NVARCHAR(10)
,EEID NVARCHAR(15)
,Ticker NVARCHAR(10)
,MarketValue MONEY
,TotalBalance MONEY
,CurrentDenominator MONEY
,AllocationAmount DECIMAL(18,6)
)
INSERT INTO #TEMP VALUES
('10241299', '70973', 'VEMAX', 5654.12, 241781.86, 241781.86, 1),
('10241299', '70973', 'VMGMX', 5972.14, 241781.86, 236127.74, 1),
('10241299', '70973', 'VTMGX', 35099.02, 241781.86, 230155.6, 1),
('10241299', '70973', 'VTSAX', 45529.07, 241781.86, 195056.58, 1),
('10241299', '70973', 'VFIAX', 149527.51, 241781.86, 149527.51, 1)
SELECT
PlanID
,EEID
,TotalBalance
,Ticker
,MarketValue
,CurrentDenominator
,AllocationAmount
,IncrementedSourceRatio = (MarketValue/CurrentDenominator)
INTO #TEMP2
FROM #TEMP
SELECT
PlanID
,EEID
,TotalBalance
,Ticker
,MarketValue
,CurrentDenominator
,AllocationAmount
,IncrementedSourceRatio
FROM #TEMP2
You can use a recursion. First you should define a column to order your rows, MarketValue I guess. So add ,rn = row_number() over(order by MarketValue) to your #temp2 query and than
with rcte as (
select *
,cast(IncrementedSourceRatio * AllocationAmount as DECIMAL(10,9)) AdjustedAllocation
,cast(0 as DECIMAL(10,9)) cumulAA
from #temp2
where rn=1
union all
select t2.*
,cast(t2.IncrementedSourceRatio * (t2.AllocationAmount - rcte.cumulAA - rcte.AdjustedAllocation) as DECIMAL(10,9))
,cast(rcte.cumulAA + rcte.AdjustedAllocation as DECIMAL(10,9))
from rcte
join #temp2 t2 on t2.rn= rcte.rn+1
)
select *
from rcte
order by MarketValue;
Returns
PlanID EEID TotalBalance Ticker MarketValue AllocationAmount IncrementedSourceRatio rn AdjustedAllocation cumulAA
10241299 70973 241781.8600 VEMAX 5654.1200 1.000000 0.0233 1 0.023300000 0.000000000
10241299 70973 241781.8600 VMGMX 5972.1400 1.000000 0.0252 2 0.024612840 0.023300000
10241299 70973 241781.8600 VTMGX 35099.0200 1.000000 0.1525 3 0.145193290 0.047912840
10241299 70973 241781.8600 VTSAX 45529.0700 1.000000 0.2334 4 0.188329030 0.193106130
10241299 70973 241781.8600 VFIAX 149527.5100 1.000000 1.0000 5 0.618564840 0.381435160