How to create debit / credit & Balance query in SQL

Hello Sanjeev Roy,

Please refer,

SELECT
    Id,
    RefNo,
    TransactionDate,
    AccountID,
    SUM(CASE WHEN DebitOrCredit = 'Debit' THEN Amount ELSE 0 END) AS Dr ,
    SUM(CASE WHEN DebitOrCredit = 'Credit' THEN Amount ELSE 0 END) AS Cr,
    SUM(CASE WHEN DebitOrCredit = 'Debit' THEN Amount ELSE -Amount END) OVER (PARTITION BY AccountID 
    ORDER BY TransactionDate, RefNo) AS Balance
FROM
    Transactions
GROUP BY
    Id,RefNo, TransactionDate, AccountID;

SELECT 
  account_id,
  SUM(CASE WHEN trans_type = 'credit' THEN amount ELSE 0 END) AS credits,
  SUM(CASE WHEN trans_type = 'debit' THEN amount ELSE 0 END) AS debits,
  SUM(CASE WHEN trans_type = 'credit' THEN amount WHEN trans_type = 'debit' THEN -amount ELSE 0 END) AS balance
FROM transactions
GROUP BY account_id;

Try this

SELECT id, RefNo, TransDate, Dr, Cr, SUM(Dr - Cr) OVER (ORDER BY id) + 40 AS Balance FROM TableName;

SELECT id, RefNo, TransDate, Dr, Cr, SUM(Dr - Cr) OVER (ORDER BY TransDate, id) AS Balance FROM TableName;

sanjeev roy 

You can use running total mehode

To create a query that calculates the debit, credit, and balance without using a subquery, you can use a combination of window functions and conditional statements in SQL.

Here's an example query that achieves the desired output:

SELECT id, RefNo, TransDate, 
    CASE WHEN Dr > 0 THEN Dr ELSE 0 END AS Dr,
    CASE WHEN Cr > 0 THEN Cr ELSE 0 END AS Cr,
    SUM(CASE WHEN Dr > 0 THEN Dr ELSE -Cr END) OVER (ORDER BY TransDate, id) AS Balance
FROM your_table
ORDER BY TransDate, id;

It calculates the debit, credit, and balance for each transaction based on the transaction date and ID.

I hope this helps! Let me know if you have any further questions.

Can you please provide your table structure with some sample data?

To get better responce please provide enough code like table structure with sample data and what you have done so far, so that others can better understand the problem.

WITH TransactionData AS (
    SELECT
        id,
        RefNo,
        TransDate,
        Dr,
        Cr,
        ROW_NUMBER() OVER (ORDER BY TransDate, id) AS RowNum
    FROM YourTableName
)

SELECT
    id,
    RefNo,
    TransDate,
    Dr,
    Cr,
    CASE
        WHEN RowNum = 1 THEN 'Opning balance'
        ELSE SUM(Dr - Cr) OVER (ORDER BY TransDate, id) -- Running balance
    END AS Balance
FROM TransactionData;

The query uses a Common Table Expression (CTE) named TransactionData to assign a row number (RowNum) based on the TransDate and id. Then, in the main query, it calculates the Balance using the SUM() window function over the ordered transactions.

To achieve this, you can use a combination of CASE statements and the SUM() window function over an ordered set of records. 

Hi Sanjeev,

Please find the query here; 

https://dba.stackexchange.com/questions/264098/how-to-retrieve-credit-debit-and-balance-from-more-than-one-table-in-sql-server

Hi , Please follow this 

https://www.codeproject.com/Questions/1230949/Credit-debit-balance-calculation-on-SQL