Sum inv_type is received, against invoice .

See more:

SQL

SQL-Server-2014

transaction

I want to sum of Inv_Type = Received ,if Invoice is create on 06-03-2020 which inv_type is Payanle and their Receiving on different Date, like below condition in image ,https://ibb.co/M8zZ4J0

In Image inv_ID 211(Inv_Type=Payable),is created on 06-03-2020 and their receiving(Inv_Type=Received) on different date

if i applied Date filter 06-03-2020 ,then fetched all inv_ID which create on Date 06-03-2020 and sum all receiving, which is inserted on different date against same invoice .

What I have tried:

<pre> 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,'Received',0)
    ;
 declare @StartDate date = '2020-03-06';
   declare @EndDate date = '2020-03-06';
     
  ;with E1 as
  (
      select
          r.*, 
          cast(Rec_Amount as int) as amount ,Customer_Name,'' as txtRec_amt
      from #tbl_Receivable r
      inner join #tbl_Customer c on c.Customer_ID = r.Customer_ID
    
   where ((R.Rec_Date) between @StartDate and @EndDate) 
  ),
  E2 as
  (
      select 
          Inv_ID,
          max(item_Weight) as item_Weight, 
          Customer_ID, Customer_Name,txtRec_amt,
          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,Customer_Name,txtRec_amt
  ),
  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(Customer_Name as varchar(50)) as Customer_Name,
          cast(Rec_Date as varchar(20)) as Rec_Date,
   cast(txtRec_amt as varchar(50)) as txtRec_amt,
    
          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,txtRec_amt, item_Weight, Customer_ID, Rec_Date, Payable, Received, Pending,Customer_Name
  from E4
    where Pending >0
  order by Inv_ID0