Daylon Hunt's questions

我有两个不同的查询，需要将它们合并在一起，然后将每个费率类型组聚合到最终表中。对于每个费率组（Rate_001mo、Rate_003mo 等）

SELECT EffectiveDate, 
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR012M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR024M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR036M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
FROM dbo.STAGING_FhlbRates WHERE EffectiveDate = '20250131'
group by EffectiveDate

UNION

SELECT EffectiveDate,
    SUM(CASE WHEN RateName = 'USOSFRA' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
    SUM(CASE WHEN RateName = 'USOSFRC' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
    SUM(CASE WHEN RateName = 'USOSFRF' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
    SUM(CASE WHEN RateName = 'USOSFR1' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
    SUM(CASE WHEN RateName = 'USOSFR2' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
    SUM(CASE WHEN RateName = 'USOSFR3' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
    FROM dbo.STAGING_SofrOisRates WHERE EffectiveDate = '20250131'
    GROUP BY EffectiveDate

最终结果表的列应包括生效日期，然后是每个费率组（Rate_001mo、Rate_003mo 等）

我正在尝试对下面列出的 9 个单独计算进行求和，结果使用 1-9 的瀑布图。给定帐户永远不会有 9 个不同计算中的每一个的值，但它可能在多个计算桶中都有值，因此我们只想按瀑布图顺序 (1-9) 呈现值。例如，如果给定帐户在第一个 (--1-) 计算桶和最后一个 (--9-) 计算桶中都有值，那么我只想引入第一个 (--1-) 计算值。

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

我有一个如下所示的分组表：

对于每个月末，我都希望提取最大版本 ID，以便最终获得唯一的月末日期和最大值 (VersionId)。我尝试编写代码，但无法正常工作。

select distinct c.END_OF_MONTH_DATE, c.VERSION_ID, 'Assets'
    from dbo.Contracts_Version_Details c
        inner join(
            select distinct max(c1.Version_Id) as MaxVersionId, c1.END_OF_MONTH_DATE
            from dbo.Contracts_Version_Details c1
            group by c1.End_of_month_date) mv
            on c.VERSION_ID = mv.MaxVersionId
        inner join(
                    select distinct max(c2.END_OF_MONTH_DATE) as MaxDate, c2.VERSION_ID
                    from dbo.Contracts_Version_Details c2
                    group by c2.VERSION_ID
                ) as md
                on c.END_OF_MONTH_DATE = md.MaxDate

我有一个函数目前使用 3 个参数：

CREATE FUNCTION dbo.RatesReport (@StartTapeDate DATE, @StartFcbVersionId int, @StartSvbVersionId int)
SELECT *
FROM dbo.DataTapes t
WHERE t.TapeDate = @StartTapeDate AND t.VersionId IN(@StartFcbVersionId, @StartSvbVersionId) 

我想添加一个附加参数 (@LoanType int)，进一步划分数据，但需要导出。CASE
WHEN t.SecId LIKE 'CD_%' THEN 1 ELSE 2

因此我的新查询/函数看起来像这样：

CREATE FUNCTION dbo.RatesReport (@StartTapeDate DATE, @StartFcbVersionId int, @StartSvbVersionId int, @DataType int)
SELECT *
FROM dbo.DataTapes t
WHERE t.TapeDate = @StartTapeDate AND t.VersionId IN(@StartFcbVersionId, @StartSvbVersionId) and t.SecId = @LoanType