Fees aggregate totals

I'm stumped by SQL. I'm using Dean's sqlReports customization. This works:

SELECT s.LastFirst, f.Department_Name, f.CreationDate, f.Description, f.FeeCharged, f.Fee_Balance
FROM Students s, Fee f
WHERE s.ID = f.StudentID
AND f.YearID = 22
AND f.Fee_Type_ID > 0
AND f.Fee_Category_Name = '%param1%'

This fails:

[Oh, I can edit my post -- the query below CONTAINS TYPOS that I couldn't see for the life of me. Actual solution in comment below]

SELECT s.LastFirst, f.Department_Name, f.CreationDate, f.Description, f.FeeCharged, f.Fee_Balance, ft.Payment_Method, ft.Transaction_Type
FROM Students s, Fee f, Fee_Transaction t
WHERE s.ID = f.StudentID
AND t.FeeID = f.ID
AND f.YearID = 22
AND f.Fee_Type_ID > 0
AND f.Fee_Category_Name = '%param1%'

I can't see the problem. Help?

Thanks!

-- MB

I'm stupid. Here's my final version:

SELECT s.LastFirst, f.Fee_Category_Name, f.CreationDate, f.Description, f.FeeCharged, f.Fee_Balance, t.Payment_Method, t.Transaction_Type
FROM Students s, Fee f, Fee_Transaction t
WHERE s.ID = f.StudentID
AND t.FeeID = f.ID
AND f.YearID = 22
AND f.Fee_Type_ID > 0
ORDER BY f.Fee_Category_Name

That query doesn't return a complete set of results, so something's still awry. Sorry for the triple post of unfinished business. I'll work on it again later/tomorrow.

K I figured out my confusion.

-- This only shows records where there was an actual transaction (i.e. it got paid) and does NOT show outstanding balances

SELECT s.LastFirst, f.Fee_Category_Name, f.CreationDate, f.Description, f.FeeCharged, f.Fee_Balance, t.Payment_Method, t.Transaction_Type
FROM Students s, Fee f, Fee_Transaction t
WHERE s.ID = f.StudentID
AND t.FeeID = f.ID
AND f.YearID = 22
AND f.Fee_Type_ID > 0
ORDER BY f.Fee_Category_Name

-- This shows all fees, outstanding or not

SELECT s.LastFirst, f.Fee_Category_Name, f.CreationDate, f.Description, f.FeeCharged, f.Fee_Balance
FROM Students s, Fee f
WHERE s.ID = f.StudentID
AND f.YearID = 22
AND f.Fee_Type_ID > 0
ORDER BY f.Fee_Category_Name

I was "missing" data because if there's no transaction record to match on the fee (i.e. there is still an outstanding balance), then the record doesn't show. A better query would be able to show both.

SELECT s.LastFirst, f.Fee_Category_Name, f.CreationDate, f.Description, f.FeeCharged, f.Fee_Balance, t.Payment_Method, t.Transaction_Type
FROM
Students s
INNER JOIN Fee f ON s.id = f.studentid
LEFT JOIN Fee_Transaction t ON f.ID = t.FeeID
WHERE
f.YearID = 22
AND f.Fee_Type_ID > 0
ORDER BY f.Fee_Category_Name

This will give you all fees for the YearID = 22 and the Fee_Type_ID > 0 regardless of whether it has a transaction or not. Though, if a transaction exists it will show as well.

Michael P. Moore
Student Information System Administrator
South Brunswick Public Schools
South Brunswick, NJ 08852

Daaaang nice. Thank you very much!

I'm having difficulty getting our sql reports to run after moving them to the sqlReports customization page in the Custom Bundle. I'm very new at this so that's most of the problem, but are there any more detailed instructions available other than what's available on the Help screen??

Subscribe to Comments for "Fees aggregate totals"