条件总和

debugcn 发表于 Dev

克雷格

我试图在查询中添加一个case表达式，但它位于select from语句中。当我尝试执行SQL Server时，它会返回多个错误。

UNION all
    SELECT 'Min WA APR for Canada MPL Receivables' AS [Text], ISNULL([Value],0.0) AS [Value], 'percent' AS [ValueType], [Limit], CASE WHEN [Excess] > 0 THEN 0 ELSE ABS(ISNULL([Excess],0.0)) END AS [Excess]
    FROM (
        --SELECT [part1]/[part2] AS [Value], [Limit], ([part1] - [Limit]*[part2])/(49.00 - [Limit]) AS [Excess]
        SELECT [part1]/[part2] AS [Value], [Limit], ([part1] - [Limit]*[part2])/100.0 AS [Excess]
        FROM (
            CASE isLPP WHEN 1 THEN
                SELECT SUM(RateLPP * OutstandingPrincipal * FAEligibility)
            ELSE
                SELECT SUM(APR * OutstandingPrincipal * FAEligibility)  
            END AS [part1]  
            FROM [dfc_BorrowingBaseRecords] WITH (NOLOCK)
            WHERE FAEligibility = 1
            AND Region = 'Canada'
            AND LoanType = 'MPL'
        ) first
        , (
            SELECT 44.90 AS [Limit], SUM(OutstandingPrincipal * FAEligibility) AS [part2]
            FROM [dfc_BorrowingBaseRecords] WITH (NOLOCK)
            WHERE FAEligibility = 1
            AND Region = 'Canada'
            AND LoanType = 'MPL'
        ) second
    ) third

如果设置了标志，我需要进行一次计算，如果没有设置，则需要进行另一次计算。

戴尔K

我认为以下是您正在寻找的东西。

关键是使用case总和内的表达式有条件地确定您要求和的值。

SELECT 'Min WA APR for Canada MPL Receivables' AS [Text], ISNULL([Value],0.0) AS [Value]
    , 'percent' AS [ValueType], [Limit]
    , CASE WHEN [Excess] > 0 THEN 0 ELSE ABS(ISNULL([Excess],0.0)) END AS [Excess]
FROM (
    SELECT  [part1]/[part2] AS [Value], [Limit], ([part1] - [Limit]*[part2])/100.0 AS [Excess]
    FROM (
        SELECT 44.90 AS [Limit]
            , SUM(CASE isLPP WHEN 1 THEN RateLPP ELSE APR END * OutstandingPrincipal * FAEligibility) [part1]  
            , SUM(OutstandingPrincipal * FAEligibility) [part2]
        FROM [dfc_BorrowingBaseRecords]
        WHERE FAEligibility = 1
        AND Region = 'Canada'
        AND LoanType = 'MPL'
    ) X
) Y

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。