question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft answered

Row Total is not displaying

HI @Viorel-1 ,

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-generalsql-server-transact-sql
row.png (20.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

HI @akhterhussain-3167,

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.



output.png (5.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.