Calculate remaining dues for each student in SQL Server

Last Reply 5 months ago By pandeyism

Posted 6 months ago

tblFeesGenerate

AdmissionNo

FeesMonth

Total Fees

R-001

Aug

700

R-002

Aug

700

R-003

Aug

700

 R-004

Aug

700

R-005

Aug

700

R-001

Sep

500

R-002

Sep

500

R-003

Sep

500

 R-004

Sep

500

R-005

Sep

500

tblFeesCollection

AdmissionNo

NetBal

Paid

RemBal

FeesMonth

R-001

700

500

200

Aug

R-002

700

400

300

Aug

R-003

700

350

350

Aug

R-004

700

700

0

Aug

R-005

700

700

0

Aug

Required Defaulter List

AdmissionNo

NetBal  (Previous Month Balance (Aug) + New Month generated Fees(Sep))

Paid

RemBal

R-001

200 + 500

0

700

R-002

300+500

0

800

R-003

350+500

0

850

R-004

500

0

500

R-005

500

0

500

 

SELECT fc.AdmissionNo,ReferenceNo,SName,FName,ParentID,AcademicName,ClassName,SectionName,Year,SUM(Recievable) as [Recievable],SUM(NetBal) as [NetBal],SUM(Paid) as [Paid],SUM(RemBal) as [RemBal],FPhone 
FROM tblFeesCollection as fc inner join tblStdReg as sr on sr.AdmissionNo=fc.AdmissionNo inner join tblAcademicYear as ay on sr.YearID=ay.YearID inner join tblDefClass as dc on dc.ClassID=sr.ClassID 
inner join tblDefSection as ds on ds.SectionID=sr.SectionID 
where Year=@Year and Paid= 0 or RemBal > 0 and FeeID in (select max(FeeID) from tblFeesCollection group by AdmissionNo) 
GROUP BY fc.AdmissionNo,SName,FName,ParentID,Year,ReferenceNo,AcademicName,ClassName,SectionName,FPhone;

 

select fg.AdmissionNo,RemBal + sum(Head+ExamFees) 'Total',ISNULL((SELECT Paid FROM tblFeesCollection WHERE FeesMonth='8-Aug'),0) 'Paid'
,(RemBal + sum(Head+ExamFees)) - ISNULL ((SELECT Paid FROM tblFeesCollection WHERE FeesMonth='8-Aug'),0) as [RemBal]
from tblFeesGenerate as fg inner join tblFeesCollection as fc on fg.AdmissionNo=fc.AdmissionNo where fc.FeesMonth='8-Aug'
--and fg.AdmissionNo='R-000003'
group by fg.AdmissionNo,RemBal

it is showing the following error

Msg 512, Level 16, State 1, Line 4 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Posted 5 months ago

Hi smile,

Please check the below query.

SQL

SELECT fg.AdmissionNo,RemBal + SUM(Head+ExamFees) 'Total',ISNULL((SELECT TOP 1 Paid FROM tblFeesCollection WHERE FeesMonth='8-Aug' AND fg.AdmissionNo='R-000003'),0) 'Paid'
,(RemBal + SUM(Head+ExamFees)) - ISNULL ((SELECT TOP 1 Paid FROM tblFeesCollection WHERE FeesMonth='8-Aug' AND fg.AdmissionNo='R-000003'),0) as [RemBal]
FROM tblFeesGenerate as fg INNER JOIN tblFeesCollection as fc ON fg.AdmissionNo=fc.AdmissionNo 
WHERE fc.FeesMonth='8-Aug'
AND fg.AdmissionNo='R-000003'
GROUP BY fg.AdmissionNo,RemBal