# Row Total is not displaying 3,001 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 ,
isnull(lead(BweightT, 1) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 2) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 3) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 4) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 5) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 6) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 7) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 8) over (partition by Codeitem order by ID), '0') as ,
isnull(lead(BweightT, 9) over (partition by Codeitem order by ID), '0') as 
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, , , , , , , , , , , 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, , , , , , , , , , , Total
from U
order by ord2, codeitem, ord1
`````` SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,820 questions

1. 24,136 Reputation points
2021-02-22T05:38:13.79+00:00

``````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 ,
isnull(lead(BweightT, 1) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 2) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 3) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 4) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 5) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 6) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 7) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 8) over (partition by DID,ITEMCODE order by ID), '') as ,
isnull(lead(BweightT, 9) over (partition by DID,ITEMCODE order by ID), '') as 
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, , , , , , , , , , , 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, , , , , , , , , , , Total
from U
order by ord2, DID, ord1
`````` Best regards
Melissa