First, is this something that you could do in the presentation layer?
Second, I get very nervous providing output that combines data AND totals.... I have seen some horrible instances where the data is moved into another program (looking at you excel) and then a total for a row/column is taken, forgetting that it already includes the total.... Oh, how we laughed at those sales figures
Finally...
Try this as your starter for 10, the insert scripts you provided try and insert strings into ints and dont have all the required columns -- #ConIssuance --) so feel free to adapt the following as well as providing expected output
use tempdb
go
drop table if exists #Containerno
drop table if exists #ConIssuance
drop table if exists #Dates26
go
create table #Containerno(CID int, ContNo varchar(50), ConWeight int, ConDate date)
insert into #Containerno
select 1, 'ABC234', 2200, '2019-05-28' union
select 2, 'SFKLFJD3423', 700, '2019-05-28' union
select 3, 'ABC222', 600,'2019-05-28' union
select 4, 'ABC33', 650,'2019-05-28' union
select 5, 'ADD432', 340,'2019-05-28'
create table #ConIssuance(IID int, CID int,EntryDate date,QTY int,Iweight int, FID int)
insert into #ConIssuance
select 1, 5, '2019-05-28' ,1,100, null union
select 2, 2, '2019-05-28',1,100, null union
select 3, 4, '2019-05-28',1,100, null union
select 4, 1, '2019-05-28',1,100, null union
select 5, 3, '2019-05-28',1,100 , null
DECLARE @StartDate DATE = '20190528'
DECLARE @EndDate DATE = '20190528'
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT Distinct Entrydate INTO #Dates26 FROM #ConIssuance WHERE EntryDate BETWEEN @StartDate AND @Enddate ORDER BY EntryDate
SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(2),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate
--SET @cols = STUFF(@cols, 1, 1, '')
SET @query =
N'
SELECT *
FROM (SELECT ContNo, #ConIssuance.IWeight,
DATEPART(DAY, #ConIssuance.EntryDate)as DDate
FROM #ConIssuance inner JOIN #Containerno on #Containerno.CID=#ConIssuance.CID
)prd
PIVOT (Sum(IWeight) FOR DDate IN (' + @cols + ')) AS stat'
exec sp_executesql @query, N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate
SET @query =
N'
SELECT contno, sum([28]) AS [28] FROM (
SELECT *
FROM (SELECT ContNo, #ConIssuance.IWeight,
DATEPART(DAY, #ConIssuance.EntryDate)as DDate
FROM #ConIssuance inner JOIN #Containerno on #Containerno.CID=#ConIssuance.CID
)prd
PIVOT (Sum(IWeight) FOR DDate IN (' + @cols + ')) AS stat
) AS D
GROUP BY ROLLUP (ContNo)'
exec sp_executesql @query, N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate