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'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');;
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(case when b.prdQTY is null then 0 else i.weight end ),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
),cte1 as (
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,
--For Percentage--
( Select (ISNULL(Small_Bale_weight,0))/((SELECT SUM(val)
FROM (VALUES (Bigbale_Weight),
(Small_Bale_weight )
)AS value(val)
))*100.0) as Percentage,
coalesce(Employee_QTY,0) Employee_QTY
FROM cte left join emp on cte.secnam=emp.secnam
group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
select * from cte1
where Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0
The reason why you encounter this problem is that in SQL, when a smaller integer is divided by a larger integer, you will get 0.
You need to deal with the value in the expression.
It is suggested that you can first convert the divisor to decimal, and then reserve a decimal for the final result value.
Please refer to below SQL codes:
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'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');;
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(case when b.prdQTY is null then 0 else i.weight end ),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
),cte1 as (
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,
--For Percentage--
( Select convert(decimal(18,1),(cast(ISNULL(Small_Bale_weight,0)as decimal))/((SELECT SUM(val)
FROM (VALUES (Bigbale_Weight),
(Small_Bale_weight )
)AS value(val)
)))*100) as Percentage,
coalesce(Employee_QTY,0) Employee_QTY
FROM cte left join emp on cte.secnam=emp.secnam
group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
select * from cte1
where Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0
Member
96 Points
230 Posts
Calculating Percentage row wise but it just given % of one row
Jul 02, 2019 02:38 PM|akhterr|LINK
here is my output https://ibb.co/Kmw1TmN
Contributor
3140 Points
983 Posts
Re: Calculating Percentage row wise but it just given % of one row
Jul 03, 2019 05:08 AM|Yang Shen|LINK
Hi akhterr,
The reason why you encounter this problem is that in SQL, when a smaller integer is divided by a larger integer, you will get 0.
You need to deal with the value in the expression.
It is suggested that you can first convert the divisor to decimal, and then reserve a decimal for the final result value.
Please refer to below SQL codes:
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'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');; 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(case when b.prdQTY is null then 0 else i.weight end ),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 ),cte1 as ( 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, --For Percentage-- ( Select convert(decimal(18,1),(cast(ISNULL(Small_Bale_weight,0)as decimal))/((SELECT SUM(val) FROM (VALUES (Bigbale_Weight), (Small_Bale_weight ) )AS value(val) )))*100) as Percentage, coalesce(Employee_QTY,0) Employee_QTY FROM cte left join emp on cte.secnam=emp.secnam group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY) select * from cte1 where Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0Here is result of my demo:
Best Regard,
Yang Shen
Member
96 Points
230 Posts
Re: Calculating Percentage row wise but it just given % of one row
Jul 03, 2019 05:40 AM|akhterr|LINK
Hi Yang Shen
thanks for reply ,,
i want to calculate Small_Bale_Weight/Total_weight*100 For example (2400/12800*100)..
Contributor
3140 Points
983 Posts
Re: Calculating Percentage row wise but it just given % of one row
Jul 03, 2019 08:48 AM|Yang Shen|LINK
Hi akhterr,
The result set I provided previously is Percentage=Small_Bale_Weight/Total_weight*100 already.
For example, in the second line 50.0=1600/3200*100.
Best Regard,
Yang Shen
Member
96 Points
230 Posts
Re: Calculating Percentage row wise but it just given % of one row
Jul 03, 2019 08:59 AM|akhterr|LINK
hi Yang Shen
sorry ,i could not make you clear ,that Grand Total of Total_Weight column will be divided.
Contributor
3140 Points
983 Posts
Re: Calculating Percentage row wise but it just given % of one row
Jul 05, 2019 09:55 AM|Yang Shen|LINK
Hi akhterr,
In a single query, it is not possible to get the total value of a column while trying to query each row.
For example, when we query the first row, we can't get the sum of Total_weight because the query for the other rows has not yet started.
So if you still want to get your goal, there are two options:
First, give up using 'with_select_', because in this case, your data coupling is too high;
Second, you can describe in detail how the three data: Small_Bale_weight, Bigbale_Weight and Total_Weight are queried from your table.
Knowing these information may help us meet your needs.
Best Regard,
Yang Shen