Waterfall Results for Numerous Individual Calculations

0 votes
1 answer
49 views
                          I'm trying to take the summation of 9 individual calculations, as listed below, where the results use a waterfall from 1-9. A given account will never have a value for each of the 9 different calculations, however it could have a value in more than one of the calculated buckets and so we would only want to render values in the waterfall order (1-9). For example, if a given account had a value for the first (--1-) calculated bucket and the last (--9-) calculated bucket, then I would only want to bring in the first (--1-) calculated value. 

    SELECT AccountNbr, 
    SUM(CurrSofrOn) AS CurrSofrOn,
    SUM(CurrTlpNonStableBase) AS CurrTlpNonStableBase,
    SUM(CurrTlpStableBase) AS CurrTlpStableBase,
    SUM(CurrBeta) AS CurrBeta, SUM(CurrFtp) AS CurrFtp,
    SUM(CurrBase) AS CurrBase, SUM(CurrStabilityAdj) AS CurrStabilityAdj,
    SUM(CurrTlpAdjAge) AS CurrTlpAdjAge, SUM(CurrModeled) AS CurrModeled
    
    FROM
    (
    	SELECT 
    	COALESCE(l.AcctNbr_2, l.AcctNbr_1) AS AccountNbr,
  
    	--1-
    	COALESCE(l.CurrAvgBook * l.SofrOnRate_2,0) AS CurrSofrOn,	
    	--2-
    	COALESCE(l.CurrAvgBook * l.MovingAvgBaseRt_2,0) AS CurrBase,
    	--3-
    	COALESCE(l.CurrAvgBook * l.TlpNonStableBase_2,0) AS CurrTlpNonStableBase,
    	--4-
    	COALESCE(l.CurrAvgBook * l.TlpStableBase_2,0) AS CurrTlpStableBase,
    	--5-
    	COALESCE(l.CurrAvgBook * l.Beta_2,0) AS CurrBeta,
    	--6-
    	COALESCE(l.CurrAvgBook * l.StableAdjBal_2,0) AS CurrStabilityAdj,
    	--7-
    	COALESCE(l.CurrAvgBook * l.FtpRate_2,0) AS CurrFtp,
    	--8-
    	COALESCE(l.CurrAvgBook * l.TlpAdjAge_2,0) AS CurrTlpAdjAge,
    	--9-
    	COALESCE(l.CurrAvgBook * l.FtpRateModel_2,0) AS CurrModeled
    FROM
    (  
    	SELECT 
    	n1.AcctNbr AS AcctNbr_1, n2.AcctNbr AS AcctNbr_2,
    	n1.TotalBookValueEoM AS PrevCurrBook,n2.TotalBookValueEoM AS CurrBook, n1.TotalBookValueAvg AS PrevAvgBook, n2.TotalBookValueAvg AS CurrAvgBook, 
    	n1.FtpRate_Model AS FtpRateModel_1, n2.FtpRate_Model AS FtpRateModel_2, n1.Stable_Base AS StableBase_1, n2.Stable_Base AS StableBase_2, 
    	n1.SofrOnRate AS SofrOnRate_1, n2.SofrOnRate AS SofrOnRate_2, n1.StableAdjustment_Balance AS StableAdjBal_1,n2.StableAdjustment_Balance AS StableAdjBal_2,
    	n1.FtpRate_Model_Floored AS FtpRateModelFloored_1, n2.FtpRate_Model_Floored AS FtpRateModelFloored_2, n1.TlpAdjustment_Age AS TlpAdjAge_1,
    	n2.TlpAdjustment_Age AS TlpAdjAge_2, n1.MovingAverageBaseRate AS MovingAvgBaseRt_1, n2.MovingAverageBaseRate AS MovingAvgBaseRt_2, 
    	n1.FtpRate_Final AS FtpRate_1, n2.FtpRate_Final AS FtpRate_2, n1.Beta AS Beta_1, n2.Beta AS Beta_2, n1.TlpTermInMonths_NonStable AS TlpTermInMnthsNS_1,
    	n2.TlpTermInMonths_NonStable AS TlpTermInMnthsNS_2, n1.BaseRateIndex AS BaseRateIndex_1, n2.BaseRateIndex AS BaseRateIndex_2, 
    	n1.TLP_Stable_Base AS TlpStableBase_1, n2.TLP_Stable_Base AS TlpStableBase_2, n1.TLP_NonStable_Base AS TlpNonStableBase_1, n2.TLP_NonStable_Base AS TlpNonStableBase_2
    		FROM
    		(
    			SELECT DISTINCT *  
    			FROM dbo.NonMaturityDepositResults n
    			WHERE n.TapeDate = 2024-11-30
    		) n1
    		FULL JOIN
    		(
    			SELECT DISTINCT *
    			FROM dbo.NonMaturityDepositResults n
    			WHERE n.TapeDate = '2024-10-31'
    		) n2 
    		ON n2.account_key = n1.account_key
    		and n2.Channel = n1.Channel
    		and n2.DepositType = n1.DepositType	
    	) l
    ) a
    GROUP BY  a.DepositChannel,a.DepositType


                        
Asked by Daylon Hunt (27 rep)
Mar 5, 2025, 06:37 PM
Last activity: Mar 5, 2025, 08:42 PM
Waterfall Results for Numerous Individual Calculations

Related Questions
