Query is not giving correct output of Column (Small_Bale_Weight)

Question

Post reply

Query is not giving correct output of Column (Small_Bale_Weight)

akhterhussain80

Mr or Mrs. 500

Points: 504
More actions
June 29, 2019 at 3:43 pm
Go to Answer
#3658647

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)

pietlinden

SSC Guru

Points: 63503

June 29, 2019 at 7:32 pm

Answer

#3658670

I formatted it.. I got lost in the CTE stuff because I wasn't following all the logic. I would definitely comment the chunks just to make it easier for other people to understand.

use tempdb;
go

CREATE TABLE #Category (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);
GO
INSERT INTO #Category VALUES(1,'INDIA'),(2,'INDIA(Cut)'),(3,'Pakistan'),(4,'Pakistan(Cut)')
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage');
INSERT INTO #ItemMasterFile VALUES(1,'A',1,1,100)
, (2,'B',2,2,100)
, (3,'C',3,3,100)
, (4,'D',4,null,100)
, (5,'e',5,null,100)
, (6,'f',6,null,100)
, (7,'g',4,2,100)
, (8,'h',4,3,100)
, (9,'K',2,2,100)
, (10,'L',4,3,100)
, (11,'M',2,4,100);
INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019',null,100)
, (2,3,3,3,1,'02-06-2019',null,100)
, (3,4,null,4,1,'03-06-2019',null,100)
, (4,4,null,4,1,'04-06-2019',null,100)
, (4,5,null,4,1,'04-06-2019',null,100);

INSERT INTO #Probale VALUES(1,1,1,100,'01-06-2019',null)
, (2,3,1,200,'02-06-2019',null)
, (3,11,1,200,'03-06-2019',null)
, (4,10,1,200,'08-06-2019',null)
, (3,8,1,200,'03-06-2019',null)
, (4,9,1,200,'08-06-2019',null)
, (4,9,1,200,'08-06-2019',null);

INSERT INTO #Emp_Strength VALUES(1,1,4,'01-05-2019')
, (2,3,5,'02-05-2019')
, (3,3,3,'03-05-2019')
, (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;

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

What's wrong with the result? You told us it's wrong, but not why or what the correct result set is. The SQL is returning what has been written, but we can't tell you what to change if we don't know what needs to be.

Also, it would be great if you out your code in a code snippet please, as that'll greatly improve readability.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

akhterhussain80 Mr or Mrs. 500 Points: 504 More actions · Answer 2

there is not null weight in Probale table ...as it is coming in small bale weight

Thom A SSC Guru Points: 99130 More actions · Answer 3

akhterhussain80 wrote:

there is not null weight in Probale table ...as it is coming in small bale weight

So what is the result set you're after here and why?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

akhterhussain80 Mr or Mrs. 500 Points: 504 More actions · Answer 4

in small bale weight column where 200 is coming ,there will come 0 and Mix Rummage weight will be 200..

Thom A SSC Guru Points: 99130 More actions · Answer 5

akhterhussain80 wrote:

in small bale weight column where 200 is coming ,there will come 0 and Mix Rummage weight will be 200..

From your expression ISNULL(SUM(i.weight),0)Small_Bale_weight in your CTE cte. You return the value of Small_Bale_weight in your final SELECT without changing it, and the value of Small_Bale_weight in your CTE when Secnum has a value of NULL is 200. So the value returns is currect for the value within the CTE cte.

If you're asking how that value was calculated, that's a totally different question.

Also, on a side note, there's no need to use DISTINCT when performing a GROUP BY. It only adds unnecessary overhead for the RDBMS. The GROUP BY (provided it's written correctly) will return a distinct result set.

This reply was modified 5 years, 4 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 6

Thom A wrote:

If you're asking how that value was calculated, that's a totally different question.

If we look at why it has a value of 200 we need to look at the previous highlighted expression.

First, let's expand the query the individual columns and remove the aggregation of GROUP by clause. This gives the below:

SELECT Sec.Secnam,
        b.prdQTY,
        i.weight
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
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 )
AND sec.Secnam IS NULL;

This returns 2 rows, which the same values NULL, NULL, 100. We can then expand the above SELECT to find out the appropriate IDs for your tables:

SELECT Sec.Secnam,
        b.prdQTY,
        i.weight,
        i.CodeItem,
        b.BID,
        sec.SecID
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
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 )
AND sec.Secnam IS NULL;

Unsurprisingly BID and SecID have a value of NULL, but CodeItem has the values 5 and 6.

So, for your final results, you have a value of 200 for the Small_Bale_weight has that is the SUM of ItemMasterFile.weight when SecNumhas a value of NULL (in this case as no rows were found), which is representative of the Items 'e' and 'f'.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk