SQL Case expression

Question

Post reply

SQL Case expression

Papilio

Valued Member

Points: 62
More actions
March 30, 2020 at 5:19 pm

#3739046

Hi people
I am still a beginner on SQL and I need help get a logic whereby whenever in the attached table the Account Number is A then I get the Balance as is else if the balance for all the accounts exceeds 300 then I need to get 300, else if the balance of all accounts does not exceed 300 then I get the sum of all the balances together. The query needs to return one amount. In this example, I am expecting that the query returns 400 for customer 1, being a balance of 100 for Account A + 300 since the sum of all the other balances exceed 300 and a balance of 35 for Customer 2 being the balance of 20 in Account A + the aggregate balance of 15 in the accounts since their aggregate balance does not exceed 300.
So far I have created the following Case expression in the Select statement:
Case
When Account Number ='A' then Balance
But I do not know how to continue with the rest.
Your help would be really appreciated.
Thanks
Jon

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

BTylerWhite SSCommitted Points: 1569 More actions · Answer 1

Hey Jon,

Please see the following article on formatting code for questions:

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

In the meantime, I've replicated your table using the following:

CREATE TABLE #CustomerAccounts
(
    CustomerID int NOT NULL,
    AccountNumber char (1) NOT NULL,
    Balance int NOT NULL
);

INSERT INTO #CustomerAccounts (CustomerID, AccountNumber, Balance)
VALUES (1, 'A', 100),
       (1, 'B', 200),
       (1, 'C', 500),
       (1, 'D', 20),
       (1, 'E', 15),
       (1, 'F', 80),
       (2, 'A', 20),
       (2, 'B', 10),
       (2, 'C', 5);

Are you looking for something like this?

WITH CTE
AS
(
    SELECT CustomerID,
           AccountNumber,
           Balance,
           SUM(   CASE
                       WHEN AccountNumber = 'A'
                            THEN 0
                       ELSE Balance
                  END
              ) OVER (PARTITION BY CustomerID) AS SumBalance
    FROM #CustomerAccounts
)
SELECT CustomerID,
       AccountNumber,
       Balance + CASE
                      WHEN CTE.SumBalance > 300
                           THEN 300
                      ELSE CTE.SumBalance
                 END AS Balance
FROM CTE
WHERE AccountNumber = 'A';

I used the SUM window function to calculate the balances outside of the "A" AccountNumbers inside of the CTE. I applied the logic I think you're looking for afterwards to sum the balances up to that 300 threshold.