Subtract calculated columns

Question

Post reply

Subtract calculated columns

pedroccamara

SSC-Addicted

Points: 431
More actions
December 4, 2022 at 11:29 am

#4121720
Hello all
I am sorry to bother but I really can't find the solution for this one.
I have an accounting table with VALUE and NATURE (of the value).
I need to know if the NATURE is 'D' than add this number to a column named Debit. If not, add it to a column named Credit. Then I need a Net column to subtract both values. I did this with CASE WHEN and I got the 2 columns. But the 3rd column I don't know how to get it. Can you help me?
Also, do you know any good SQL course that I could take, for free? Because I've already done one and that solution is not there.
Thank you all in advance
- This topic was modified 1 year, 11 months ago by pedroccamara.

December 5, 2022 at 8:36 am

This was removed by the editor as SPAM

December 29, 2022 at 10:34 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

If you have done a SQL course it will have covered how to do this; you have just not fully understood.

I reccommend you read books by Itzik Ben-Gan starting with T-SQL Fundamentals.

In this particular case I suspect you need to understand:

https://blog.sqlauthority.com/2020/11/18/sql-server-logical-processing-order-of-the-select-statement/

Here are 3 approaches:

SELECT ...
    ,CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END AS Credit
    ,CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END AS Debit
    ,(CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END)
        - (CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END) As Balance
FROM YourTable Y;

SELECT ...
    ,X.Credit, X.Debit
    ,X.Credit - X.Debit AS Balance
FROM YourTable Y
    CROSS APPLY
    (
        VALUES
        (
            CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END
            ,CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END
        )
    ) X (Credit, Debit);

WITH cte
AS
(
    SELECT ...
        ,CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END AS Credit
        ,CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END AS Debit
    FROM YourTable Y
)
SELECT ...
    ,Credit, Debit
    ,Credit - Debit AS Balance
FROM cte;

This reply was modified 1 year, 11 months ago by Ken McKelvey.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 2

Amazing!! Such a good content! I will try later

Thank you so much Ken for all your help

This reply was modified 1 year, 11 months ago by pedroccamara.

Annataliax Newbie Points: 2 More actions · Answer 3

Ken McKelvey wrote:

If you have done a SQL course it will have covered how to do this; you have just not fully understood.

I reccommend you read books by Itzik Ben-Gan starting with T-SQL Fundamentals.

In this particular case I suspect you need to understand:

https://blog.sqlauthority.com/2020/11/18/sql-server-logical-processing-order-of-the-select-statement/lolbeans

Here are 3 approaches:

SELECT ...
    ,CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END AS Credit
    ,CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END AS Debit
    ,(CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END)
        - (CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END) As Balance
FROM YourTable Y;

SELECT ...
    ,X.Credit, X.Debit
    ,X.Credit - X.Debit AS Balance
FROM YourTable Y
    CROSS APPLY
    (
        VALUES
        (
            CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END
            ,CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END
        )
    ) X (Credit, Debit);

WITH cte
AS
(
    SELECT ...
        ,CASE WHEN Y.Nature <> 'D' THEN Y.Credit + Y.[Value] ELSE Y.Credit END AS Credit
        ,CASE WHEN Y.Nature = 'D' THEN Y.Debit + Y.[Value] ELSE Y.Debit END AS Debit
    FROM YourTable Y
)
SELECT ...
    ,Credit, Debit
    ,Credit - Debit AS Balance
FROM cte;

I should take your advice "you read books by Itzik Ben-Gan starting with T-SQL Fundamentals."

pedroccamara SSC-Addicted Points: 431 More actions · Answer 4

Hi Ken

Thank you for your message.

In this case, we don't have the columns "debit" or "credit". We just have the column value and the other column "Nature". So,

IF Nature = "D" Then Value *-1 ELSE 0 END AS Debit

IF Nature = "C" Then Value ELSE 0 END AS Credit

Now, could you please tell me how would you do it on those 3 approaches?

Thanks a lot

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

Seems like you might be looking for a total?!:

SELECT yt.account_number, SUM(ca1.Debit) AS Total_Debits, SUM(ca1.Credit) AS Total_Credits, 
    SUM(ca1.Debit) - SUM(ca1.Credit) AS Net_Amount
FROM dbo.your_table yt
CROSS APPLY (
    SELECT CASE WEHN yt.Nature = 'D' Then yt.Value *-1 ELSE 0 END AS Debit,
        CASE WEHN yt.Nature = 'C' Then yt.Value  ELSE 0 END AS Credit
) AS ca1
GROUP BY yt.account_number

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".