sql query is not giving proper result of Column (Small_Bale_Weight)
here is my complete data and query
CREATE TABLE #Catagory (CID INT,CName VARCHAR(50))
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50))
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT,weight int)
CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,Bpqty INT,Entrydate DATETIME , DelID int,Bweight int)
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,weight int,Entrydate DATETIME,DelID int)
CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME)
INSERT INTO #Catagory VALUES(1,'INDIA')
INSERT INTO #Catagory VALUES(2,'INDIA(Cut)')
INSERT INTO #Catagory VALUES(3,'Pakistan')
INSERT INTO #Catagory VALUES(4,'Pakistan(Cut)')
INSERT INTO #Sections VALUES(1,'HR')
INSERT INTO #Sections VALUES(2,'Baby')
INSERT INTO #Sections VALUES(3,'Ladies')
INSERT INTO #Sections VALUES(4,'Mix Rammage')
INSERT INTO #ItemMasterFile VALUES(1,'A',1,1,100)
INSERT INTO #ItemMasterFile VALUES(2,'B',2,2,100)
INSERT INTO #ItemMasterFile VALUES(3,'C',3,3,100)
INSERT INTO #ItemMasterFile VALUES(4,'D',4,null,100)
INSERT INTO #ItemMasterFile VALUES(5,'e',5,null,100)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,null,100)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,2,100)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,3,100)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,2,100)
INSERT INTO #ItemMasterFile VALUES(10,'L',4,3,100)
INSERT INTO #ItemMasterFile VALUES(11,'M',2,4,100)
INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019',null,100)
INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,'02-06-2019',null,100)
INSERT INTO #Bigbalprd VALUES(3,4,null,4,1,'03-06-2019',null,100)
INSERT INTO #Bigbalprd VALUES(4,4,null,4,1,'04-06-2019',null,100)
INSERT INTO #Bigbalprd VALUES(4,5,null,4,1,'04-06-2019',null,100)
INSERT INTO #Probale VALUES(1,1,1,100,'01-06-2019',null)
INSERT INTO #Probale VALUES(2,3,1,200,'02-06-2019',null)
INSERT INTO #Probale VALUES(3,11,1,200,'03-06-2019',null)
INSERT INTO #Probale VALUES(4,10,1,200,'08-06-2019',null)
INSERT INTO #Probale VALUES(3,8,1,200,'03-06-2019',null)
INSERT INTO #Probale VALUES(4,9,1,200,'08-06-2019',null)
INSERT INTO #Probale VALUES(4,9,1,200,'08-06-2019',null)
INSERT INTO #Emp_Strength VALUES(1,1,4,'01-05-2019')
INSERT INTO #Emp_Strength VALUES(2,3,5,'02-05-2019')
INSERT INTO #Emp_Strength VALUES(3,3,3,'03-05-2019')
INSERT INTO #Emp_Strength VALUES(4,4,7,'04-05-2019')
DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01-06-2019'
SET @Enddate = '09-06-2019'
;WITH emp
as
(
select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s
left join #Emp_Strength e on s.secid=e.secid
where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)
group by Secnam
),cte
AS
(
SELECT DISTINCT Sec.Secnam,
ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,
ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(i.weight),0)Small_Bale_weight
--ISNULL(SUM(emp.QTY),0)Employee_QTY
FROM #ItemMasterFile i
LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem
LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem
Full Outer Join #Sections sec on i.SecID=sec.SecID
--left join Emp_Strength emp on emp.SecID = sec.SecID
--FULL OUTER JOIN Sections s ON i.SecID = s.SecID
where (b.DelID is null and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null )
and
(b1.DelID is null and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )
GROUP BY sec.Secnam
) SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight,
( SELECT SUM(val)
FROM (VALUES (Bigbale_QTY)
, (Smallbale_QTY)
) AS value(val)
) AS Total_QTY,
( SELECT SUM(val)
FROM (VALUES (Bigbale_Weight),
(Small_Bale_weight )
)AS value(val)
) AS Total_Weight ,coalesce(Employee_QTY,0) Employee_QTY
FROM cte left join emp on cte.secnam=emp.secnam
HERE IS MY OUTPUT IN WHICH COLUMN OF SMALL BALE WEIGHT RESULT IS NOT COMING CORRECT..
HERE IS LINK OF MY RESULT IMAGE (https://ibb.co/TBXFzyq)
