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
mbates
Wed, 09/12/2012 - 1:13pm
Permalink
never mind
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
mbates
Wed, 09/12/2012 - 1:20pm
Permalink
haha no idea what I'm doing
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.
mbates
Thu, 09/13/2012 - 1:31pm
Permalink
K I figured out my confusion.
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.
michael.moore
Thu, 09/13/2012 - 1:48pm
Permalink
RE:
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
mbates
Tue, 09/18/2012 - 11:32am
Permalink
THANKS!
Daaaang nice. Thank you very much!
ddannar
Wed, 10/31/2012 - 2:13pm
Permalink
sqlReports
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??