Exclude Rows based on column condition in SQL Server

Last Reply 10 months ago By dharmendr

Posted 10 months ago

I have tables structure like this

tblFeesGenerate

AdmissionNo

Month

Total

R-02

10-Oct

500

R-03

10-Oct

500

R-04

10-Oct

500

R-02

11-Nov

500

R-03

11-Nov

500

R-04

11-Nov

500

R-05

11-Nov

500

R-06

11-Nov

500

R-07

11-Nov

500

tblFeesCollection

AdmissionNo

Month

Recievable

Arrears

NetBal

Paid

RemBal

Collection Date

R-02

10-Oct

500

250

250

200

50

10.10.18

R-03

10-Oct

500

0

500

300

200

10.10.18

R-04

10-Oct

500

0

500

0

500

10.10.18

R-04

11-Nov

500

500

1000

700

300

08.11.18

 Output of My Query

AdmissionNo

Recievable

Arrears

NetBal

Month

R-02

500

50

550

11-Nov

R-03

500

200

700

11-Nov

R-04

500

300

800

11-Nov

R-05

500

Null

500

11-Nov

R-06

500

Null

500

11-Nov

R-07

500

Null

500

11-Nov

Now look above at tblFeesCollection. Here AdmissionNo- R-04 has paid his fees for the month of 11-Nov. hence this AdmissionNo should not be exists in the list.

R2 and R3 also paid 200 and 300 respectively as per tblFeesCollection only for the month of October but has not paid for the month of Nov. hence these 3 students are defaulter for the month of Nov.

As R4 has paid something so he is not defaulter for the month of Nov. Now i want the list of defaulter students who has not paid for the month of Nov which are R2, R3.

It should be like this

AdmissionNo

Recievable

Arrears

NetBal

Month

R-02

500

50

550

11-Nov

R-03

500

200

700

11-Nov

R-05

500

Null

500

11-Nov

R-06

500

Null

500

11-Nov

R-07

500

Null

500

11-Nov

 Query written by me

select fg.AdmissionNo,SName,FName,AcademicName,ClassName,SectionName ,FPhone,Month

,sum(Total) 'Recievable',RemBal 'Arrears', isnull((sum(Total)),0) + isnull((RemBal),0) 'NetBal'

from tblFeesGenerate as fg left join tblFeesCollection as fc on fg.AdmissionNo=fc.AdmissionNo

inner join tblStdReg as sr on fg.AdmissionNo=sr.AdmissionNo

inner join tblAcademicYear as ay on sr.YearID=ay.YearID inner join tblDefClass as dc on sr.ClassID=dc.ClassID inner join tblDefSection as ds on sr.SectionID=ds.SectionID

where fc.AdmissionNo is null or fc.AdmissionNo is not null

and Year = '2018' and Month='11-Nov'

and FeeID in (Select Max(FeeID) from tblFeesCollection group by AdmissionNo)

Group by fg.AdmissionNo,SName,FName,AcademicName,ClassName,SectionName,FPhone,Month,RemBal order by AdmissionNo;

 

Posted 10 months ago

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

AdmissionNo Recievable Arrears NetBal Month
R-02 500 50 550 11-Nov
R-03 500 200 700 11-Nov
R-05 500 NULL 500 11-Nov
R-06 500 NULL 500 11-Nov
R-07 500 NULL 500 11-Nov