How to manage credit debit amount in ms sql in table

The simplest form is to have another column, say DrCr, which is just Char(1) and will have value 'D' or 'C'. This is for the transaction table.

You will need to store balances separately. What date would be your year closing? So, if your year closing is on 31-March, have another table ItemId, BalanceAsOn and Balance.

In this store data like, 1, 01-Apr-2016, 5 for each item.

While processing, you will need to calculate opening balance from this table and transactions up-to that date. Then show transactions for the specified period and keep calculating balance.

In SQL Server 2012, new ranking functions provided to access value from previous record, please look into those for calculating balance.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
The simplest form is to have another column, say DrCr, which is just Char(1) and will have value 'D' or 'C'. This is for the transaction table.
You will need to store balances separately. What date would be your year closing? So, if your year closing is on 31-March, have another table ItemId, BalanceAsOn and Balance.
In this store data like, 1, 01-Apr-2016, 5 for each item.
While processing, you will need to calculate opening balance from this table and transactions up-to that date. Then show transactions for the specified period and keep calculating balance.
In SQL Server 2012, new ranking functions provided to access value from previous record, please look into those for calculating balance.