I have three tables: Table 1 contains the data of each account opening balance with opening date and acct type
Table Name : LedgerMaster
nID(pk)IDENTITY(1,1) | LEDGERACCTID | LEDGERACCTNAME | ACCTTYPE | OPENINGDATE | OPENINGBALANCE |
---|---|---|---|---|---|
1 | 101 | CASHACCT | DEBIT | 2024-01-01 | 10000 |
2 | 102 | BANKACCT157 | DEBIT | 2024-01-01 | 0 |
3 | 201 | VENDOR1 | CREDIT | 2024-01-01 | 2000 |
4 | 202 | VENDOR2 | CREDIT | 2024-01-01 | 0 |
5 | 411 | Sales Revenue | CREDIT | 2024-01-01 | 0 |
Table 2: CreditMaster
this table contains all the daily credit transactions
nID | date | invoiceno | ledgerid | ledgername | cramount | description |
---|---|---|---|---|---|---|
1 | 2024-01-01 | inv-01 | 411 | Sales Revenue | 2000 | sale |
2 | 2024-01-01 | inv-02 | 411 | Sales Revenue | 1000 | sales |
3 | 2024-01-02 | Inv-03 | 411 | Sales Revenue | 3000 | sale |
4 | 2024-01-01 | PO-01 | 202 | VENDOR1 | 1700 | PURCHASE |
5 | 2024-01-02 | PO-02 | 202 | VENDOR1 | 2000 | PURCHASE |
6 | 2024-01-01 | inv-04 | 411 | Sales Revenue | 2500 | sale |
7 | 2024-01-02 | inv-05 | 411 | Sales Revenue | 1500 | sale |
8 | 2024-01-01 | PAY-01 | 101 | CASHACCT | 5000 | Payment |
Table 3 : DebitMaster
nID | date | invocieno | ledgerid | ledgername | dramount | description |
---|---|---|---|---|---|---|
1 | 2024-01-01 | inv-01 | 101 | CASHACCT | 2000 | sale |
2 | 2024-01-01 | inv-02 | 101 | CASHACCT | 1000 | sale |
3 | 2024-01-01 | inv-03 | 101 | CASHACCT | 3000 | sale |
4 | 2024-01-01 | inv-04 | 102 | BANKACCT157 | 2500 | sale |
5 | 2024-01-02 | inv-05 | 102 | BANKACCT157 | 1500 | sale |
6 | 2024-01-01 | PO-01 | 412 | PurchaseAcct | 5000 | Purchase |
7 | 2024-01-01 | PAY-01 | 202 | VENDOR | 5000 | PAYMENT |
Expectation for example if I select the cashaaact(101) and date range like I select account and date from >=2024-01-01 and dateto<=2024-01-01
NID | iNVOICENO | DATE | DEBIT | CREDIT | BALANCE | DESCRIPTION | header 1 | header 2 | |
---|---|---|---|---|---|---|---|---|---|
oPENING BALANCE | 2024-01-01 | 1000 | 00 | 10000 | OPENING BALANCE | cell 1 | cell 2 | ||
INV-01 | 2024-01-01 | 2000 | 00 | 12000 | SALE | cell 3 | cell 4 | ||
inv-02 | 2024-01-01 | 1000 | 00 | 13000 | SALE | ||||
PAY-01 | 2024-01-01 | 00 | 5000 | 8000 | PAYMENT |
When I change the date the opening balance will be become 8000 on 2024-01-02
If the account type is credit the opening balance will add on credit side and if accounttype is debit os opening balance will adsd on debit side
I do not have any idea how to do that using sql server query
Sam Hasan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
For this solution I union all 3 tables together and give them each a type.
I can then use CASE to control how each column behaves for the main select.
A “SUM(amount) OVER..” will calculate the running sum.
SELECT nID,
invno,
date,
CASE TYPE
WHEN ' CREDIT' THEN ABS(amount)
ELSE 0.00 END as Credit,
CASE TYPE
WHEN 'DEBIT' THEN ABS(amount)
ELSE 0.00 END as Debit,
SUM(amount) OVER(ORDER BY date, TYPE desc
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS Balance,
Description
FROM
(
SELECT nID, OPENINGDATE as date, 'oPENING BALANCE' as invno, LEDGERACCTID as ledgerid, LEDGERACCTNAME as ledgername, 'OPEN' as TYPE, OPENINGBALANCE as amount, 'OPENING balance' as description
FROM LedgerMaster
UNION ALL
SELECT nID, date, invoiceno as invno, ledgerid, ledgername, ' CREDIT' as TYPE, -cramount as amount, description
FROM CreditMaster
UNION ALL
SELECT nID, date, invocieno as invno, ledgerid, ledgername, 'DEBIT' as TYPE, dramount as amount, description
FROM DebitMaster
) CD
WHERE ledgerid='101'
AND DATE BETWEEN '2024-01-01' AND '2024-02-02'
ORDER BY DATE, TYPE desc
fiddle
nID | invno | date | Credit | Debit | Balance | Description |
---|---|---|---|---|---|---|
1 | oPENINGBALANCE | 2024-01-01 | 0.00 | 0.00 | 10000 | OPENINGbalance |
1 | inv-01 | 2024-01-01 | 0.00 | 2000.00 | 12000 | sale |
2 | inv-02 | 2024-01-01 | 0.00 | 1000.00 | 13000 | sale |
3 | inv-03 | 2024-01-01 | 0.00 | 3000.00 | 16000 | sale |
8 | PAY-01 | 2024-01-01 | 5000.00 | 0.00 | 11000 | Payment |
15