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.

    Thank you all in advance

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

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

  • This was removed by the editor as SPAM

  • 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

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

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

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