I want Pending amount to calculate Correctly, Below is Data with expected output.
Note: in Invoice table when ever rate will be same then that invoice qty will be sum and multiple to rate, if rate will not be same then it display separately .
As you can see invoice no.104 and 105 rate is same ,then their qty get sum accordingly same rate, those invoice in which rate is not same then it will display their rate separately, as you can see invoice 103.
I am retrieving data between two date from '2021-05-01' to '2021-05-30'. before date '2021-05-01' there is two invoice which amount total is 3600 and i received amount in May 3000 ,then Pending amount must be 600 show
Create table #tbl_item (item_ID int,Name varchar(50))
Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50))
CREATE TABLE #tbl_InvoiceM(Inv_ID INT,Level_Four_ID int,Inv_date date,inv_amt Decimal(10,2))
Create Table #tbl_INvoiceD (DID int,item_ID int,INV_ID int, qty int,Rate int)
Create table #tbl_Received (R_ID int,Level_Four_ID int,Rec_AMt int,R_Date date);
insert into #tbl_item values (11,'19Liter')
Insert into #tbl_Account_L_Four values (1001,'akhter')
insert into #tbl_InvoiceM values(101,1,'2021-04-25',2000)
insert into #tbl_InvoiceD values(1,11,101,4,500)
insert into #tbl_InvoiceM values(102,1,'2021-04-28',1600)
insert into #tbl_InvoiceD values(1,11,102,8,200)
insert into #tbl_InvoiceM values(103,1,'2021-05-01',550)
insert into #tbl_InvoiceD values(1,11,103,2,150),
(2,11,101,5,50)
insert into #tbl_InvoiceM values(104,1,'2021-05-02',4900)
insert into #tbl_InvoiceD values(1,11,104,5,500),
(2,11,104,4,600)
insert into #tbl_InvoiceM values(105,1,'2021-05-04',4900)
insert into #tbl_InvoiceD values(1,11,105,5,500),
(2,11,105,4,600)
insert into #tbl_Received values(1,2000,1001,'2021-05-02')
insert into #tbl_Received values(1,1000,1001,'2021-05-03');




