It seems that you experienced a problem when item_Weight is only specified in one row of a group. Then try this long sequence too:
drop table if exists #tbl_Receivable, #tbl_Customer
Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50));
insert into #tbl_Customer values (1,'Akhter'), (2,'Hussian');
INSERT INTO #tbl_Receivable VALUES
(111,211,1,'2020-03-06',5000,'Payable',0),
(112,211,1,'2020-03-07',2000,'Received',0),
(113,222,1,'2020-03-08',8000,'Payable',0),
(114,223,2,'2020-03-08',2000,'Payable',0),
(115,222,1,'2020-03-09',4000,'Received',0),
(116,224,1,'2020-03-10',15000,'Payable',500),
(117,211,1,'2020-03-12',1000,'Received',0),
(118,224,1,'2020-03-11',15000,'Payable',0)
---
;
with E1 as
(
select
r.*,
cast(Rec_Amount as int) as amount
from #tbl_Receivable r
inner join #tbl_Customer c on c.Customer_ID = r.Customer_ID
),
E2 as
(
select
Inv_ID,
max(item_Weight) as item_Weight,
Customer_ID,
min(Rec_Date) as Rec_Date,
isnull(sum(case Inv_type when 'Payable' then amount end), 0) as Payable,
isnull(sum(case Inv_type when 'Received' then amount end), 0) as Received
from E1
group by Inv_ID, Customer_ID
),
E3 as
(
select
Inv_ID as Inv_ID0,
cast(Inv_ID as varchar(10)) as Inv_ID,
cast(item_Weight as varchar(10)) as item_Weight,
cast(Customer_ID as varchar(10)) as Customer_ID,
cast(Rec_Date as varchar(20)) as Rec_Date,
Payable,
Received,
Payable - Received as Pending
from E2
),
E4 as
(
select *
from E3
union all
select
2147483647,
'',
'',
'',
'TOTAL',
sum(Payable),
sum(Received),
sum(Pending)
from E3
)
select Inv_ID, item_Weight, Customer_ID, Rec_Date, Payable, Received, Pending
from E4
order by Inv_ID0
If still there are issues, then show the data to reproduce them, and the expected results.
Hi @akhter hussain ,
Thanks for your update.
Actually my query was working with your original post.
Right now you already recevied your updated answer.
We still recommend you to post as much sample data as possible when you post one question so that we could work with it properly at the beginning.
Thanks for your understanding.
Best regards
Melissa