CODEWITHSUNDEEP
sqlsql-servert-sql
Craig
Craig

Reputation: 1225

Conditional summed value

I am trying to add a case expression in my query but it is inside of a select from statement. SQL Server returns multiple errors when I try to execute it.

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

I need to do one calculation if a flag is set and another if it is not.

Upvotes: 0

Views: 46

Answers (1)

Dale K
Dale K

Reputation: 27202

I think the following is what you are looking for.

The key point is to use the case expression inside the sum to conditionally determine what value you are summing.

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

Upvotes: 3

Related Questions