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.Exclude Rows based on column condition in SQL Server<p>Hi&nbsp;smile,</p> <p>You need to add another condition in the where clause for filter the record. The condition should be get the&nbsp;AdmissionNo from&nbsp;tblFeesCollection where the month is&nbsp;11-Nov and&nbsp;Paid greater than 0 and pass&nbsp;AdmissionNo to not in condition.</p> <p>Check the below test query.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">DECLARE @tblFeesGenerate AS TABLE (AdmissionNo VARCHAR(10),Month VARCHAR(10),Total INT) INSERT INTO @tblFeesGenerate VALUES(&#39;R-02&#39;,&#39;10-Oct&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-03&#39;,&#39;10-Oct&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-04&#39;,&#39;10-Oct&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-02&#39;,&#39;11-Nov&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-03&#39;,&#39;11-Nov&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-04&#39;,&#39;11-Nov&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-05&#39;,&#39;11-Nov&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-06&#39;,&#39;11-Nov&#39;,500) INSERT INTO @tblFeesGenerate VALUES(&#39;R-07&#39;,&#39;11-Nov&#39;,500) DECLARE @tblFeesCollection AS TABLE(AdmissionNo VARCHAR(10),Month VARCHAR(10),Recievable INT,Arrears INT,NetBal INT,Paid INT,RemBal INT,CollectionDate VARCHAR(10)) INSERT INTO @tblFeesCollection VALUES(&#39;R-02&#39;,&#39;10-Oct&#39;,500,250,250,200,50,&#39;10.10.18&#39;) INSERT INTO @tblFeesCollection VALUES(&#39;R-03&#39;,&#39;10-Oct&#39;,500,0,500,300,200,&#39;10.10.18&#39;) INSERT INTO @tblFeesCollection VALUES(&#39;R-04&#39;,&#39;10-Oct&#39;,500,0,500,0,500,&#39;10.10.18&#39;) INSERT INTO @tblFeesCollection VALUES(&#39;R-04&#39;,&#39;11-Nov&#39;,500,500,100,700,300,&#39;08.11.18&#39;) select fg.AdmissionNo,sum(Total) &#39;Recievable&#39;,RemBal &#39;Arrears&#39;, isnull((sum(Total)),0) + isnull((RemBal),0) &#39;NetBal&#39;,fg.Month from @tblFeesGenerate as fg left join @tblFeesCollection as fc on fg.AdmissionNo=fc.AdmissionNo where fc.AdmissionNo is null or fc.AdmissionNo is not null and fg.Month=&#39;11-Nov&#39; and fc.AdmissionNo NOT IN(SELECT AdmissionNo FROM @tblFeesCollection WHERE Month = &#39;11-Nov&#39; AND Paid &gt; 0) Group by fg.AdmissionNo,fg.Month,RemBal order by AdmissionNo</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr> <td>AdmissionNo</td> <td>Recievable</td> <td>Arrears</td> <td>NetBal</td> <td>Month</td> </tr> <tr> <td>R-02</td> <td>500</td> <td>50</td> <td>550</td> <td>11-Nov</td> </tr> <tr> <td>R-03</td> <td>500</td> <td>200</td> <td>700</td> <td>11-Nov</td> </tr> <tr> <td>R-05</td> <td>500</td> <td>NULL</td> <td>500</td> <td>11-Nov</td> </tr> <tr> <td>R-06</td> <td>500</td> <td>NULL</td> <td>500</td> <td>11-Nov</td> </tr> <tr> <td>R-07</td> <td>500</td> <td>NULL</td> <td>500</td> <td>11-Nov</td> </tr> </tbody> </table> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/490448/Exclude-Rows-based-on-column-condition-in-SQL-Server/https://www.aspforums.net:443/Threads/490448/Exclude-Rows-based-on-column-condition-in-SQL-Server/Tue, 04 Dec 2018 23:46:26 GMT