Calculate opening and closing balance with running balance using three tables [closed]

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

New contributor

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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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
</code>
<code>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 </code>
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

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật