Script for a receivable aging report from an MS SQL database table

Question

1.00/5 (1 vote)

See more:

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.

Posted 28-Apr-24 10:45am

Solution Hall

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2024-04-28T19:12:00

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]