Below is my data ,i am trying to display or Sum Received amount in one row,but it is not displaying data in one row,means inv_Id 224 is repeating two times,which should not be.
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-15',1000,'Received',0)
;
What I have tried:
;with cte as (
SELECT a.Inv_ID,item_Weight,b.Customer_ID,min(a.Rec_Date) Rec_Date
,SUM((CASE WHEN a.INV_TYPE= 'Payable' THEN CAST(a.Rec_Amount as int) ELSE 0 END)) as Payable,
SUM((CASE WHEN a.INV_TYPE= 'Received' THEN CAST(a.Rec_Amount as int) ELSE 0 END)) as Received,
SUM((CASE WHEN a.INV_TYPE= 'Payable' THEN CAST(a.Rec_Amount as int) ELSE 0 END)) -
SUM((CASE WHEN a.INV_TYPE= 'Received' THEN CAST(a.Rec_Amount as int) ELSE 0 END)) as Pending
from #tbl_Receivable a
inner join #tbl_Customer b on a.Customer_ID = b.Customer_ID
GROUP BY a.INV_ID,b.Customer_Name,a.item_Weight,b.Customer_ID)
select cast(Inv_ID as varchar(100)) Inv_ID
, cast(item_Weight as varchar(100)) item_Weight
, cast(Customer_ID as varchar(100)) Customer_ID
, cast(Rec_Date as varchar(100)) Rec_Date
,Payable,Received,Pending
from cte
union all
select '','','','Total',sum(Payable),sum(Received),sum(pending)
from cte
Ouput which is coming.
rec — ImgBB[
^]
https://ibb.co/HCPbh5N