Row Total is not displaying

Analyst_SQL 3,551 Reputation points
2021-02-20T06:07:04.42+00:00

HI @Viorel ,

as your Provided solution ,l little modified ,when i am executing it ,then it is not display all rows total ,
Note: You used DID instead of Codeitem,but i used Codeitem instead of DID 

 with I as  
 (  
     select *  
     from Dispatch_BD  
     where DID in (1195)  
 ),  
 N as  
 (  
     select *, row_number() over (partition by codeitem order by ID) - 1 as n  
     from I  
 ),  
 P as  
 (  
     select *, cast(Bweight as varchar(max)) as BweightT, n % 10 + 1 as c, n / 10 + 1 as r  
     from N  
 ),  
 G as  
 (  
     select *,   
         isnull(lead(BweightT, 0) over (partition by Codeitem order by ID), '0') as [1],  
         isnull(lead(BweightT, 1) over (partition by Codeitem order by ID), '0') as [2],  
         isnull(lead(BweightT, 2) over (partition by Codeitem order by ID), '0') as [3],  
         isnull(lead(BweightT, 3) over (partition by Codeitem order by ID), '0') as [4],  
         isnull(lead(BweightT, 4) over (partition by Codeitem order by ID), '0') as [5],  
         isnull(lead(BweightT, 5) over (partition by Codeitem order by ID), '0') as [6],  
         isnull(lead(BweightT, 6) over (partition by Codeitem order by ID), '0') as [7],  
         isnull(lead(BweightT, 7) over (partition by Codeitem order by ID), '0') as [8],  
         isnull(lead(BweightT, 8) over (partition by Codeitem order by ID), '0') as [9],  
         isnull(lead(BweightT, 9) over (partition by Codeitem order by ID), '0') as [10]  
     from P  
 ),  
 R as  
 (  
     select *   
     from G   
     where c = 1  
 ),  
 F as  
 (  
     select   
         case r when 1 then t.Descriptionitem else '0' end as Item,   
         case r when 1 then cast((select count(*) from I where Codeitem = R.Codeitem) as varchar(max)) else '0' end as QTY,  
         case when lead(DID) over (partition by DID order by r) is null   
             then cast((select sum(Bweight) from I where CodeItem = R.CodeItem) as varchar(max)) else '0' end as Total,  
         R.*  
     from R  
     inner join ItemMasterFile t on t.CodeItem = R.Codeitem  
 ),  
 GT as  
 (  
     select   
         cast(count(*) as varchar(max)) as GrandTotalQTY,   
         cast(sum(I.Bweight) as varchar(max)) as GrandTotalBWeight  
     from I  
 ),  
 E as  
 (  
     select distinct codeitem  
     from I  
 ),  
 U as  
 (  
     select codeitem, Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2  
     from F  
     union all  
     select codeitem, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 2, 1  
     from E  
     union all  
     select NULL, 'Grand Total', GrandTotalQTY, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', GrandTotalBWeight, 1, 2  
     from GT  
 )  
 select Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total  
 from U  
 order by ord2, codeitem, ord1

70174-row.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,772 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-22T05:38:13.79+00:00

    HI @Analyst_SQL ,

    Please refer below: 

    DROP TABLE IF EXISTS #item,#DistpatchM,#Distpatch_BD  
   
CREATE TABLE #item (ItemCode int,ItemName varchar(max))      
CREATE TABLE #DistpatchM(DID int,E_Date date)  
CREATE TABLE #Distpatch_BD(ID int, Itemcode int, DID int, DisQTY int, Bweight int)  
      
INSERT INTO #item VALUES(1001, 'A')  
INSERT INTO #item VALUES(1002, 'B')  
INSERT INTO #item VALUES(1003, 'C')  
INSERT INTO #item VALUES(1004, 'D')  
INSERT INTO #item VALUES(1005, 'E')  
INSERT INTO #item VALUES(1006, 'F')  
INSERT INTO #item VALUES(1007, 'G')  
INSERT INTO #DistpatchM values(111,'2019-05-28')  
INSERT INTO #DistpatchM values(112,'2018-05-29')                 
INSERT INTO #Distpatch_BD values(1,1001,111,1 ,500)  
INSERT INTO #Distpatch_BD values(2,1002,111 ,1,600)  
INSERT INTO #Distpatch_BD values(3,1003,111 ,1,800)  
INSERT INTO #Distpatch_BD values(4,1006,112,1,900)  
INSERT INTO #Distpatch_BD values(5,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(6,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(7,1006,112,1,900)  
INSERT INTO #Distpatch_BD values(8,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(9,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(10,1006,112,1,900)  
INSERT INTO #Distpatch_BD values(11,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(12,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(13,1006,112,1,4500)  
INSERT INTO #Distpatch_BD values(14,1006,112,1,650)  
INSERT INTO #Distpatch_BD values(14,1001,112,1,400)  
  
;with I as  
(  
    select *  
    from #Distpatch_BD   
where DID in (112)  
),  
N as  
(  
    select *, row_number() over (partition by DID,ITEMCODE order by ID) - 1 as n  
    from I  
),  
P as  
(  
    select *, cast(Bweight as varchar(max)) as BweightT, n % 10 + 1 as c, n / 10 + 1 as r  
    from N  
) ,  
G as  
(  
    select *,   
        isnull(lead(BweightT, 0) over (partition by DID,ITEMCODE order by ID), '') as [1],  
        isnull(lead(BweightT, 1) over (partition by DID,ITEMCODE order by ID), '') as [2],  
        isnull(lead(BweightT, 2) over (partition by DID,ITEMCODE order by ID), '') as [3],  
        isnull(lead(BweightT, 3) over (partition by DID,ITEMCODE order by ID), '') as [4],  
        isnull(lead(BweightT, 4) over (partition by DID,ITEMCODE order by ID), '') as [5],  
        isnull(lead(BweightT, 5) over (partition by DID,ITEMCODE order by ID), '') as [6],  
        isnull(lead(BweightT, 6) over (partition by DID,ITEMCODE order by ID), '') as [7],  
        isnull(lead(BweightT, 7) over (partition by DID,ITEMCODE order by ID), '') as [8],  
        isnull(lead(BweightT, 8) over (partition by DID,ITEMCODE order by ID), '') as [9],  
        isnull(lead(BweightT, 9) over (partition by DID,ITEMCODE order by ID), '') as [10]  
    from P  
) ,  
R as  
(  
    select *   
    from G   
    where c = 1  
)  
,  
Max as  
(  
    select ITEMCODE,max(r) max   
    from G   
    where c = 1  
	group by ITEMCODE  
),  
F as  
(  
    select   
        case r when 1 then t.ItemName else '' end as Item,   
    case r when 1 then cast((select count(*) from I where ITEMCODE = R.ITEMCODE and did=r.did) as varchar(max)) else '' end as QTY,  
	case when r=max then  
        cast((select sum(Bweight) from I where ITEMCODE = R.ITEMCODE) as varchar(max))   
		else '' end   
		as Total,  
        R.*  
    from R  
    inner join #Item t on t.ITEMCODE = R.ITEMCODE  
	left join max m on m.ITEMCODE=r.ITEMCODE	   
),  
GT as  
(  
    select cast(count(*) as varchar(max)) as GrandTotalQTY,   
        cast(sum(I.Bweight) as varchar(max)) as GrandTotalBWeight  
    from I	   
)  
,  
E as  
(  
    select distinct DID  
    from I  
),  
U as  
(  
    select DID, Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2  
    from F  
    union all  
    select DID, '', '', '', '', '', '', '', '', '', '', '', '', '', 2, 1  
    from E  
    union all  
    select NULL, 'Grand Total', GrandTotalQTY, '', '', '', '', '', '', '', '', '', '', GrandTotalBWeight, 1, 2  
    from GT  
)  
select Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total  
from U  
order by ord2, DID, ord1

    70467-output.png

    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.

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest