Exclude Rows based on column condition in SQL Server

Hi smile,

You need to add another condition in the where clause for filter the record. The condition should be get the AdmissionNo from tblFeesCollection where the month is 11-Nov and Paid greater than 0 and pass AdmissionNo to not in condition.

Check the below test query.

SQL

DECLARE @tblFeesGenerate AS TABLE (AdmissionNo VARCHAR(10),Month VARCHAR(10),Total INT)
INSERT INTO @tblFeesGenerate VALUES('R-02','10-Oct',500)
INSERT INTO @tblFeesGenerate VALUES('R-03','10-Oct',500)
INSERT INTO @tblFeesGenerate VALUES('R-04','10-Oct',500)
INSERT INTO @tblFeesGenerate VALUES('R-02','11-Nov',500)
INSERT INTO @tblFeesGenerate VALUES('R-03','11-Nov',500)
INSERT INTO @tblFeesGenerate VALUES('R-04','11-Nov',500)
INSERT INTO @tblFeesGenerate VALUES('R-05','11-Nov',500)
INSERT INTO @tblFeesGenerate VALUES('R-06','11-Nov',500)
INSERT INTO @tblFeesGenerate VALUES('R-07','11-Nov',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('R-02','10-Oct',500,250,250,200,50,'10.10.18')
INSERT INTO @tblFeesCollection VALUES('R-03','10-Oct',500,0,500,300,200,'10.10.18')
INSERT INTO @tblFeesCollection VALUES('R-04','10-Oct',500,0,500,0,500,'10.10.18')
INSERT INTO @tblFeesCollection VALUES('R-04','11-Nov',500,500,100,700,300,'08.11.18')

select fg.AdmissionNo,sum(Total) 'Recievable',RemBal 'Arrears', isnull((sum(Total)),0) + isnull((RemBal),0) 'NetBal',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='11-Nov' and
fc.AdmissionNo NOT IN(SELECT AdmissionNo FROM @tblFeesCollection WHERE Month = '11-Nov' AND Paid > 0)
Group by fg.AdmissionNo,fg.Month,RemBal 
order by AdmissionNo

Output