Calculate Percentage based on another columns using Common Table Expression (CTE) in SQL Server

Last Reply 5 months ago By pandeyism

Posted 5 months ago

I am trying to calculate percentage (Small_Bale_Weight/(Grand Total 0f Total_Weight column) *100) , just it giving me on row percentage 

here is my whole query with data, highlight in Pink for Percentage calculation 

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

here is link of  above query output https://ibb.co/474hZ1t

You are viewing reply posted by: pandeyism 5 months ago.
Posted 5 months ago Modified on 5 months ago

Hi akhter,

Refer below sample.

SQL

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')
INSERT INTO #Category VALUES(2,'INDIA(Cut)')
INSERT INTO #Category VALUES(3,'Pakistan')
INSERT INTO #Category 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 #Sections VALUES(5,'T-Shirst')
INSERT INTO #Sections VALUES(6,'Scrap')
INSERT INTO #Sections VALUES(7,'Scrit')
INSERT INTO #Sections VALUES(8,'Men')
INSERT INTO #Sections VALUES(9,'HHR')
INSERT INTO #Sections VALUES(10,'Sports')
INSERT INTO #Sections VALUES(11,'m-HR')
INSERT INTO #Sections VALUES(12,'M-Baby')
INSERT INTO #Sections VALUES(13,'M-Ladies')
INSERT INTO #Sections VALUES(14,'M-Mix Rammage')
INSERT INTO #Sections VALUES(15,'m--Shirst')
INSERT INTO #Sections VALUES(16,'M-Scrap')
INSERT INTO #Sections VALUES(17,'M-Scrit')
INSERT INTO #Sections VALUES(18,'M-Men')
INSERT INTO #Sections VALUES(19,'M-HHR')
INSERT INTO #Sections VALUES(20,'M-Sports')
 
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(CASE WHEN b.prdQTY is null THEN 0 ELSE  i.weight END ),0)Small_Bale_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 )
 
GROUP BY sec.Secnam
),cte1 AS (
SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight, Small_Bale_weight + Bigbale_Weight Total_Weight,
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 secnam
       ,Smallbale_QTY
       ,Small_Bale_weight
       ,Bigbale_Weight
       ,Total_Weight
       ,CAST(ROUND((CONVERT(DECIMAL,Small_Bale_Weight)/(SELECT SUM(Total_Weight)tot FROM cte1)*100),2) AS NUMERIC(18,2)) Percentage
FROM cte1
WHERE  Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_Weight+Employee_QTY<>0

DROP TABLE #Category
DROP TABLE #Sections
DROP TABLE #ItemMasterFile
DROP TABLE #Bigbalprd
DROP TABLE #Probale
DROP TABLE #Emp_Strength

Output

secnam Smallbale_QTY Small_Bale_weight Bigbale_Weight Total_Weight Percentage
Baby 3 300 0 300 25
HR 1 100 100 200 8.33
Ladies 1 100 100 200 8.33
Mix Rammage 2 200 200 400 16.67
T-Shirst 0 0 100 100 0