ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Calculate Percentage based on another columns using Common Table Expression (CTE) in SQL Server<p>Hi <a class="username" rel="akhter"> akhter</a>,</p> <p>Refer below sample.</p> <p><span style="text-decoration: underline;"><strong>SQL</strong></span> </p> <pre class="brush: 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,&#39;INDIA&#39;) INSERT INTO #Category VALUES(2,&#39;INDIA(Cut)&#39;) INSERT INTO #Category VALUES(3,&#39;Pakistan&#39;) INSERT INTO #Category VALUES(4,&#39;Pakistan(Cut)&#39;) INSERT INTO #Sections VALUES(1,&#39;HR&#39;) INSERT INTO #Sections VALUES(2,&#39;Baby&#39;) INSERT INTO #Sections VALUES(3,&#39;Ladies&#39;) INSERT INTO #Sections VALUES(4,&#39;Mix Rammage&#39;) INSERT INTO #Sections VALUES(5,&#39;T-Shirst&#39;) INSERT INTO #Sections VALUES(6,&#39;Scrap&#39;) INSERT INTO #Sections VALUES(7,&#39;Scrit&#39;) INSERT INTO #Sections VALUES(8,&#39;Men&#39;) INSERT INTO #Sections VALUES(9,&#39;HHR&#39;) INSERT INTO #Sections VALUES(10,&#39;Sports&#39;) INSERT INTO #Sections VALUES(11,&#39;m-HR&#39;) INSERT INTO #Sections VALUES(12,&#39;M-Baby&#39;) INSERT INTO #Sections VALUES(13,&#39;M-Ladies&#39;) INSERT INTO #Sections VALUES(14,&#39;M-Mix Rammage&#39;) INSERT INTO #Sections VALUES(15,&#39;m--Shirst&#39;) INSERT INTO #Sections VALUES(16,&#39;M-Scrap&#39;) INSERT INTO #Sections VALUES(17,&#39;M-Scrit&#39;) INSERT INTO #Sections VALUES(18,&#39;M-Men&#39;) INSERT INTO #Sections VALUES(19,&#39;M-HHR&#39;) INSERT INTO #Sections VALUES(20,&#39;M-Sports&#39;) &nbsp; INSERT INTO #ItemMasterFile VALUES(1,&#39;A&#39;,1,1,100) INSERT INTO #ItemMasterFile VALUES(2,&#39;B&#39;,2,2,100) INSERT INTO #ItemMasterFile VALUES(3,&#39;C&#39;,3,3,100) INSERT INTO #ItemMasterFile VALUES(4,&#39;D&#39;,4,null,100) INSERT INTO #ItemMasterFile VALUES(5,&#39;e&#39;,5,null,100) INSERT INTO #ItemMasterFile VALUES(6,&#39;f&#39;,6,null,100) INSERT INTO #ItemMasterFile VALUES(7,&#39;g&#39;,4,2,100) INSERT INTO #ItemMasterFile VALUES(8,&#39;h&#39;,4,3,100) INSERT INTO #ItemMasterFile VALUES(9,&#39;K&#39;,2,2,100) INSERT INTO #ItemMasterFile VALUES(10,&#39;L&#39;,4,3,100) INSERT INTO #ItemMasterFile VALUES(11,&#39;M&#39;,2,4,100) INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,&#39;01-06-2019&#39;,null,100) INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,&#39;02-06-2019&#39;,null,100) INSERT INTO #Bigbalprd VALUES(3,4,null,4,1,&#39;03-06-2019&#39;,null,100) INSERT INTO #Bigbalprd VALUES(4,4,null,4,1,&#39;04-06-2019&#39;,null,100) INSERT INTO #Bigbalprd VALUES(4,5,null,4,1,&#39;04-06-2019&#39;,null,100) &nbsp; INSERT INTO #Probale VALUES(1,1,1,100,&#39;01-06-2019&#39;,null) INSERT INTO #Probale VALUES(2,3,1,200,&#39;02-06-2019&#39;,null) INSERT INTO #Probale VALUES(3,11,1,200,&#39;03-06-2019&#39;,null) INSERT INTO #Probale VALUES(4,10,1,200,&#39;08-06-2019&#39;,null) INSERT INTO #Probale VALUES(3,8,1,200,&#39;03-06-2019&#39;,null) INSERT INTO #Probale VALUES(4,9,1,200,&#39;08-06-2019&#39;,null) INSERT INTO #Probale VALUES(4,9,1,200,&#39;08-06-2019&#39;,null) &nbsp; INSERT INTO #Emp_Strength VALUES(1,1,4,&#39;01-05-2019&#39;) INSERT INTO #Emp_Strength VALUES(2,3,5,&#39;02-05-2019&#39;) INSERT INTO #Emp_Strength VALUES(3,3,3,&#39;03-05-2019&#39;) INSERT INTO #Emp_Strength VALUES(4,4,7,&#39;04-05-2019&#39;) DECLARE @StartDate DATETIME, @Enddate DATETIME SET @StartDate = &#39;01-06-2019&#39; SET @Enddate = &#39;09-06-2019&#39; ;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&nbsp; 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&nbsp;&nbsp; (b1.DelID IS NULL AND b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate IS NULL ) &nbsp; 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 &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ,Smallbale_QTY &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ,Small_Bale_weight &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ,Bigbale_Weight &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ,Total_Weight &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ,CAST(ROUND((CONVERT(DECIMAL,Small_Bale_Weight)/(SELECT SUM(Total_Weight)tot FROM cte1)*100),2) AS NUMERIC(18,2)) Percentage FROM cte1 WHERE&nbsp; Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_Weight+Employee_QTY&lt;&gt;0 DROP TABLE #Category DROP TABLE #Sections DROP TABLE #ItemMasterFile DROP TABLE #Bigbalprd DROP TABLE #Probale DROP TABLE #Emp_Strength</pre> <p><span style="text-decoration: underline;"><strong>Output</strong></span></p> <table border="1"> <tbody> <tr style="height: 15.0pt;"> <td style="height: 15.0pt; width: 48pt;" width="64" height="20"><strong>secnam</strong></td> <td style="width: 48pt;" width="64"><strong>Smallbale_QTY</strong></td> <td style="width: 48pt;" width="64"><strong>Small_Bale_weight</strong></td> <td style="width: 48pt;" width="64"><strong>Bigbale_Weight</strong></td> <td style="width: 48pt;" width="64"><strong>Total_Weight</strong></td> <td style="width: 48pt;" width="64"><strong>Percentage</strong></td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">Baby</td> <td align="right">3</td> <td align="right">300</td> <td align="right">0</td> <td align="right">300</td> <td align="right">25</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">HR</td> <td align="right">1</td> <td align="right">100</td> <td align="right">100</td> <td align="right">200</td> <td align="right">8.33</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">Ladies</td> <td align="right">1</td> <td align="right">100</td> <td align="right">100</td> <td align="right">200</td> <td align="right">8.33</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">Mix Rammage</td> <td align="right">2</td> <td align="right">200</td> <td align="right">200</td> <td align="right">400</td> <td align="right">16.67</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">T-Shirst</td> <td align="right">0</td> <td align="right">0</td> <td align="right">100</td> <td align="right">100</td> <td align="right">0</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/242867/Calculate-Percentage-based-on-another-columns-using-Common-Table-Expression-CTE-in-SQL-Server/https://www.aspforums.net:443/Threads/242867/Calculate-Percentage-based-on-another-columns-using-Common-Table-Expression-CTE-in-SQL-Server/Thu, 04 Jul 2019 01:44:56 GMT