How to create debit / credit & Balance query in SQL

Question

I need expected output id RefNo TransDate Dr Cr Balance ----------- ---------- ---------- --------------------- --------------------- Opning balance 40 3 003 2013-01-03 300.00 0.00 340 4 004 2013-01-04 0.00 100.00 240 5 004 2013-01-05 200.00 0.00 440 Best Regards Sanjeev

Satya Karki · Answer

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

Aman Gupta · Answer

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

Amit Mohanty · Answer

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.

Tuhin Paul · Answer

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.

Tuhin Paul · Answer

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

Jignesh Kumar · Answer

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;

Mohammad Hussain · Answer

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;

Rijwan Ansari · Answer

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

Anandu G Nath · Answer

sanjeev roy You can use running total mehode

Jayraj Chhaya · Answer

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.

Aravind Govindaraj · Answer

Hi , Please follow this https://www.codeproject.com/Questions/1230949/Credit-debit-balance-calculation-on-SQL

How to create debit / credit & Balance query in SQL

Answers (11)