Add column of Closing_Qty and Closing_Weight,sum column total in footer

Question

Below is query ,which is giving result of date wise ,but i want to add two column of Closing_Qty and Closing_Weight ,sum total of each column display in footer.

DECLARE @StartDate  date = '03-06-2020';  
 DECLARE @enddate date = '05-06-2020';  
  
Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50))  
 Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,delid int)  
 Create table #DispatchBM (DID int,Name varchar(50),Date date,Del int)  
 Create table #Dispatch_BD (ID int ,DID int,Codeitem int,QTY int,BWeight int,Delidd int)  
      
 INSERT INTO #ItemMasterFile VALUES  
   (1,'A','Bigbale')  
 , (2,'B','Bigbale')  
 , (3,'C','Bigbale')  
 , (4,'D','Bigbale')  
 , (5,'e','Bigbale')  
 , (6,'f','Bigbale')  
 , (7,'g','Bigbale')  
 , (8,'h','Bigbale')  
 , (9,'K','Bigbale')  
 , (10,'L','Bigbale')  
 , (11,'M','Bigbale');  
      
      
 INSERT INTO #Bigbalprd VALUES  
 (111,1,1,500,'03-06-2020',null)  
 ,(112,2,1,200,'03-06-2020',null)  
 ,(113,1,1,300,'03-06-2020',null)  
 ,(114,6,1,100,'04-06-2020',null)  
 ,(115,1,1,200,'04-06-2020',null)  
 ,(116,1,1,300,'04-06-2020',null)  
 ,(117,7,1,100,'05-06-2020',null)  
 ,(118,5,1,200,'05-06-2020',null)  
 ,(119,8,1,300,'06-06-2020',null)  
      
 Insert into #DispatchBM Values  
 (1001,'Akhter','03-06-2020',null)  
 ,(1002,'Irfan','05-06-2020',null)  
 Insert into #Dispatch_BD Values  
 (11,1001,1,1,500,null)  
 ,(12,1001,2,1,200,null)  
 ,(13,1001,1,1,300,null)  
 ,(14,1002,7,1,100,null)  
 ,(15,1002,5,1,200,null)  
  
  
 ;with cte as (  
select a.CodeItem ,upper(a.Descriptionitem) item_Name,(b.EntryDate)B_Date,sum(Bpqty) B_QTY,sum(Bweight) B_Weight  
from #ItemMasterFile a  
left join #Bigbalprd b   
on a.CodeItem=b.CodeItem  
where convert(date,b.EntryDate,105) between @startdate and @enddate and b.delID is null  
group by a.CodeItem,a.Descriptionitem,b.EntryDate  
)  
,cte1 as (  
select a.CodeItem,upper(a.Descriptionitem) item_Name,  Date,sum(qty) D_QTY,sum(Bweight) D_Weight  
from #ItemMasterFile a  
left join #Dispatch_BD c  
on c.codeitem=a.codeitem  
left join #DispatchBM d  
on d.DID=c.DID   
where convert(date,date,105) between @startdate and @enddate and c.Delidd is null and d.Del is null  
group by a.codeitem,a.Descriptionitem,d.date  
)  
  
select c.codeitem,upper(c.Descriptionitem) item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight  
into #t  
from #ItemMasterFile c   
left join cte a on a.codeitem=c.codeitem  
left join cte1 b on a.codeitem=b.codeitem  
and a.B_Date=b.date  
where c.Packsize='Bigbale'  
DECLARE @cols NVARCHAR (MAX)  
  
SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'  
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
set @cols=SUBSTRING(@cols,2,len(@cols)-1)  
  
DECLARE @cols1 NVARCHAR (MAX)  
  
SET @cols1 = (SELECT DISTINCT ',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'  
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'  
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'  
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)  
  
DECLARE @cols2 NVARCHAR (MAX)  
  
SET @cols2 = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight] NVARCHAR(1000)'  
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight] NVARCHAR(1000)'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
DECLARE @cols3 NVARCHAR (MAX)  
  
SET @cols3 = (SELECT DISTINCT ',''B_Qty'' [' + CONVERT(NVARCHAR, B_date, 23)  +'],''B_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + ']'  
+',''D_QTY'' [' + CONVERT(NVARCHAR, B_date, 23) + '] ' +',''D_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + '] '  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
set @cols3=SUBSTRING(@cols3,2,len(@cols3)-1)  
  
DECLARE @query NVARCHAR(MAX)  
SET @query = '    
  
select '''' codeitem,'''' item_Name,'+@cols3+'  
UNION ALL  
SELECT cast(codeitem as varchar(10)) codeitem,item_Name,' + @cols1 + '  
            FROM (  
    SELECT codeitem,item_Name,  CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE  
    FROM (  
        select * from #t  
    )s  
    UNPIVOT  
    (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p  
) src  
             PIVOT   
             (  
			MAX(VALUE) FOR Name IN (' + @cols + ')  
) pvt  
            '     
EXEC SP_EXECUTESQL @query

Below query which displaying Closing balance of qty and weight,

select f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Closing_QTY],(f.Bigbale_weight-f.Dispatch_Weight) as [Closing_Weight] from (  
select e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (  
select a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]  
from ItemMasterFile a  
  
left join Bigbalprd b on a.CodeItem=b.CodeItem  
 where a.Packsize ='bigbale' and b.delID is null  
  group by a.Descriptionitem) e  
left join Dispatch_BD c on e.CodeItem=c.CodeItem   
 where c.Delidd is null    
group by e.Name  
  
)f  
 ORDER BY f.Name

Note: Closing weight and qty ,will be next day opening

Accepted Answer

Hi @Analyst_SQL ,

It seems not easy to modify your dynamic SQL. Can you insert the results of dynamic SQL into a temporary table and then add columns?

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Add column of Closing_Qty and Closing_Weight,sum column total in footer

0 additional answers