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.
13,273 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,181 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 No comments

0 additional answers

Sort by: Most helpful