Daylon Hunt's questions

Tenho duas consultas diferentes que preciso unir em UNION e, então, agregar cada grupo de tipo de taxa em uma tabela final. Para cada grupo de taxa (Rate_001mo, Rate_003mo, etc.)

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

As colunas da tabela de resultados finais devem incluir a Data Efetiva e, em seguida, cada grupo de taxas (Taxa_001mês, Taxa_003mês, etc.)

Estou tentando fazer a soma de 9 cálculos individuais, conforme listado abaixo, onde os resultados usam uma cascata de 1 a 9. Uma determinada conta nunca terá um valor para cada um dos 9 cálculos diferentes, no entanto, ela pode ter um valor em mais de um dos buckets calculados e, portanto, só queremos renderizar valores na ordem da cascata (1 a 9). Por exemplo, se uma determinada conta tivesse um valor para o primeiro (--1-) bucket calculado e o último (--9-) bucket calculado, então eu só gostaria de trazer o primeiro (--1-) valor calculado.

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

Tenho uma tabela agrupada que se parece com esta:

Para cada fim de mês, quero puxar o Max Version Id para que no final eu tenha datas de Fim de mês exclusivas e max(VersionId)'s . Tentei escrever o código, mas não está funcionando corretamente.

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

Tenho uma função que atualmente usa 3 parâmetros:

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) 

Gostaria de adicionar um parâmetro adicional (@LoanType int) que bifurque ainda mais os dados, mas ele precisaria ser derivado.
CASE WHEN t.SecId LIKE 'CD_%' THEN 1 ELSE 2

Para que minha nova consulta/função fique mais ou menos assim:

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