Add row wise total and Column wise total .

Question

I want row wise total and column wise total,
Note :

In Row wise Total below condition will apply,and generate to column Balance Bale and second one is Balance Lbs

B_QTY will get minus from D_QTY (Balance Bale).
B_Weight will get minus from D_Weight (Balance Lbs.)

Below query is attached.

23033-query.txt

Answer 1

Hi @Analyst_SQL ，

Please refer below:

drop table if exists #ItemMasterFile  
drop table if exists #Bigbalprd  
drop table if exists #DispatchBM  
drop table if exists #DispatchDB  
drop table if exists #t  
  
Create table #ItemMasterFile (item_ID int,item_Name varchar(50))  
Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date)  
Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)  
Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)  
  
INSERT INTO #ItemMasterFile VALUES  
  (1,'A')  
, (2,'B')  
, (3,'C')  
, (4,'D')  
, (5,'e')  
, (6,'f')  
, (7,'g')  
, (8,'h')  
, (9,'K')  
, (10,'L')  
, (11,'M');  
  
  
INSERT INTO #Bigbalprd VALUES  
(111,1,1,500,'03-06-2020')  
,(112,2,1,200,'03-06-2020')  
,(113,1,1,300,'03-06-2020')  
,(114,6,1,100,'04-06-2020')  
,(115,1,1,200,'04-06-2020')  
,(116,1,1,300,'04-06-2020')  
,(117,7,1,100,'05-06-2020')  
,(118,5,1,200,'05-06-2020')  
,(119,8,1,300,'06-06-2020')  
  
Insert into #DispatchBM Values  
(1001,'Akhter','03-06-2020')  
,(1002,'Irfan','05-06-2020')  
Insert into #DispatchDB Values  
(11,1001,1,1,500)  
,(12,1001,2,1,200)  
,(13,1001,1,1,300)  
,(14,1002,7,1,100)  
,(15,1002,5,1,200)  
  
DECLARE @StartDate  date = '03-06-2020';  
DECLARE @enddate date = '06-06-2020';  
  
;with cte as (  
select a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight  
from #ItemMasterFile a  
left join #Bigbalprd b   
on a.item_ID=b.item_ID  
where convert(date,B_Date,105) between @startdate and @enddate  
group by a.item_ID,a.item_Name,B_Date  
)  
,cte1 as (  
select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight  
from #ItemMasterFile a  
left join #DispatchDB c  
on c.item_ID=a.item_ID  
left join #DispatchBM d  
on d.D_ID=c.D_ID   
where convert(date,D_Date,105) between @startdate and @enddate  
group by a.item_ID,a.item_Name,d.D_Date  
)  
  
select c.item_ID,upper(c.item_Name) 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.item_ID=c.item_ID  
left join cte1 b on a.item_ID=b.item_ID  
and a.B_Date=b.D_Date  
  
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 @SUM1 NVARCHAR (MAX)  
  
SET @SUM1=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) +'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
SET @SUM1=LEFT(@SUM1,LEN(@SUM1)-1)  
  
DECLARE @SUM2 NVARCHAR (MAX)  
  
SET @SUM2=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) +'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
SET @SUM2=LEFT(@SUM2,LEN(@SUM2)-1)  
  
DECLARE @SUM3 NVARCHAR (MAX)  
  
SET @SUM3=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) +'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
SET @SUM3=LEFT(@SUM3,LEN(@SUM3)-1)  
  
DECLARE @SUM4 NVARCHAR (MAX)  
  
SET @SUM4=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) +'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
SET @SUM4=LEFT(@SUM4,LEN(@SUM4)-1)  
  
DECLARE @SUM NVARCHAR (MAX)  
  
SET @SUM='CAST(SUM('+@SUM1+')-SUM('+@SUM2+') AS VARCHAR),CAST(SUM('+@SUM3+')-SUM('+@SUM4+')AS VARCHAR),'  
  
DECLARE @query NVARCHAR(MAX)  
SET @query = '    
  
select '''' item_id,'''' item_Name,'''' [Balance Bale],'''' [Balance Lbs.],'+@cols3+'  
UNION ALL  
SELECT cast(item_id as varchar(10)) item_id,item_Name,'+@SUM + @cols1 + '  
            FROM (  
    SELECT item_ID,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  
group by item_id,item_Name,' + @cols   
              
EXEC SP_EXECUTESQL @query  

Best regards
Melissa

---

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.