First things first; it is always helpful to post code that has some formatting to it, which makes it a lot easier for people to read what you are trying to do
SELECT distinct
RTRIM(Students.StudentNumber)
, RTRIM(Students.StudentSurname)
, RTRIM(Students.StudentFirstNames)
, RTRIM(CourseFeePayment.PaymentDue)
from Students
, CourseFeePayment
, CourseFeePayment_Join
where Students.StudentNumber = CourseFeePayment.StudentNumber
and CourseFeePayment.CourseFeePaymentID = CourseFeePayment_Join.C_PaymentID
and CourseFeePayment.SchoolYear = @d1
and CourseFeePayment.Student_Class = @d2
and CourseFeePayment_Join.Month = @d3
and CourseFeePayment.PaymentDue > 0
order by 2
For my next trick.... I'm going to eliminate the
RTrim
s as it does nothing for numbers, and the text should have had it done at the time of insertion. I am also going to use some
aliases on the table names, and I'm going to write in actual joins utilizing them.
Notice I can get the whole SELECT list neatly on one line
SELECT DISTINCT s.StudentNumber, s.StudentSurname, s.StudentFirstNames, p.PaymentDue
FROM Students s
INNER JOIN CourseFeePayment p ON s.StudentNumber = p.StudentNumber
INNER JOIN CourseFeePayment_Join j ON p.CourseFeePaymentID= j.C_PaymentID
WHERE CourseFeePayment.SchoolYear = @d1
AND CourseFeePayment.Student_Class = @d2
AND CourseFeePayment_Join.Month = @d3
AND CourseFeePayment.PaymentDue > 0
ORDER BY 2
Now I can see a problem forming with this query.... What this is actually going to do is pull a list of students who had a payment due and also made a payment in the selected month.
What we really are going to need to do is
aggregate the amounts that are paid, by student, for the selected period
SELECT p.StudentNumber, SUM(p.TotalPaid)
FROM CourseFeePayment p
INNER JOIN CourseFeePayment_Join j ON p.CourseFeePaymentID = j.C_PaymentID
WHERE p.StudentClass = @d2
AND j.Month = @d3
GROUP BY p.StudentNumber
I really don't want to go too much further with this; as I do not know any of the data that you do have and what mechanisms are in place to INSERT payment records- this is not a design I would be using. Depending on what is all in going on would tell me if I was going to use this aggregation as a subquery, store it in a
#temp table, or use it once via a CTE
What I would recommend is working with this in SSMS or some other SQL client application, as it would be much more versatile and convenient.
I would also recommend that when all is said and done with this query that you just convert it over to a
Stored Procedure
.