Invoice with payment history

Transact-SQL
1

Hi,

I have 3 table

  1. Initial invoice table
  2. ReInvoice Table and
  3. Payment Table

explanation:

  1. Initially we invoiced 1000
  2. they paid only 200 for first time
  3. again we reinvoiced 800
  4. they paid only 100 and said that they will pay only 500 out of 700 balance
  5. we reinvoiced again only 500

My table script like below

create table ##Inv(InvNo NVARCHAR(10), InvDate Date, InvAmount decimal(18,2))

insert into ##Inv Values ('1001','2023-01-01',1000)

create table ##ReInv(ReInvNo NVARCHAR(10), ReInvDate Date, ReInvAmount decimal(18,2), lvl int)

insert into ##ReInv values ('1001','2023-01-03',800,2)

insert into ##ReInv values ('1001','2023-01-05',500,3)

create table ##Payment(InvNo NVARCHAR(10), PaidDate Date, PaidAmount decimal(18,2), lvl int)

insert into ##Payment values ('1001','2023-01-02',200,1)

insert into ##Payment values ('1001','2023-01-04',100,2)

insert into ##Payment values ('1001','2023-01-08',500,3)

select * from ##Inv

select * from ##ReInv

select * from ##Payment

drop table ##Inv

drop table ##ReInv

drop table ##Payment

User's image

I need the result like below (pls leave about the table structure)

Copy

Create table ##Result (InvNo NVARCHAR(10), InvDate Date, InvAmount decimal(18,2), TotalPaid decimal(18,2),
	_1stPaidDate Date, _1stPaidAmount decimal(18,2), 
	_1stReInvDate Date, _1stReInvAmount decimal(18,2), _2ndPaidDate Date, _2ndPaidAmount decimal(18,2),
	_2ndReInvDate Date, _2ndReInvAmount decimal(18,2), _3rdPaidDate Date, _3rdPaidAmount decimal(18,2))

insert into ##Result values ('1001','2023-01-01',1000.00,800.00,'2023-01-02',200.00,'2023-01-03',800.00,
'2023-01-04',100.00,'2023-01-05',500.00,'2023-01-08',500.00)

select * from ##Result



The column will increase dynamically based on payment and ReInvoice table

Thanks in Advance