I want to make a receivable aging report for a customer using MS SQL Server Database. I am using MS SQLSERVER 2017.
Below the image of the ledger table is shown.
tbl_Ledger
Date V.Type V # Debit Credit
1-Jan-24 Sales 1 5,000 -
11-Jan-24 Receipt 1 - 1,750
12-Jan-24 Sales 2 2,750 -
24-Jan-24 Receipt 2 - 2,675
27-Jan-24 Sales 3 1,180 -
6-Feb-24 Receipt 3 - 950
10-Feb-24 Sales 4 1,150 -
20-Feb-24 Receipt 4 - 1,850
22-Feb-24 Sales 5 3,500 -
11-Mar-24 Receipt 5 - 1,755
14-Mar-24 Sales 6 2,800 -
15-Mar-24 Receipt 6 - 1,120
22-Mar-24 Sales 7 3,570 -
5-Apr-24 Receipt 7 - 2,150
7-Apr-24 Sales 8 3,450 -
11-Apr-24 Receipt 8 - 1,520
15-Apr-24 Sales 9 3,640 -
20-Apr-24 Receipt 9 - 2,265
22-Apr-24 Sales 10 7,650 -
Total Sales Total Receipts
34,690 16,035
Net Balance As On 25-Apr-24 18,655
As can be seen the sales # 10/9/8/7 are fully included in the total balance while the sales # 6 is partially included. The amount still due for payment from the sale # 6 is 345. Now I want my aging report to look like this as on 25th April 2024
Aging Report
Due From V # Balance Aged Days
14-Mar-24 Sales # 6 345 42
22-Mar-24 Sales # 7 3,570 34
7-Apr-24 Sales # 8 3,450 18
15-Apr-24 Sales # 9 3,640 10
22-Apr-24 Sales # 10 7,650 3
What script should I write to get this report in output?
What I have tried:
I tried some scripts but could not get the required output. So I need the solution from scratch.