# Subtract calculated columns

• 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.

• This topic was modified 1 month, 3 weeks ago by  pedroccamara.
• 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 BalanceFROM YourTable Y;`
`SELECT ...    ,X.Credit, X.Debit    ,X.Credit - X.Debit AS BalanceFROM 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 cteAS(    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 BalanceFROM cte;`

• This reply was modified 1 month, 3 weeks ago by  Ken McKelvey.
• Amazing!! Such a good content! I will try later

Thank you so much Ken for all your help

• This reply was modified 1 month, 3 weeks ago by  pedroccamara.
• This was removed by the editor as SPAM

• 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:

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 BalanceFROM YourTable Y;`
`SELECT ...    ,X.Credit, X.Debit    ,X.Credit - X.Debit AS BalanceFROM 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 cteAS(    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 BalanceFROM cte;`

• This was removed by the editor as SPAM

• Hi Ken

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

• 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_AmountFROM dbo.your_table ytCROSS 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 ca1GROUP BY yt.account_number`

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

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