March 12, 2009 at 11:52 pm
hi,
i have created an enrollment system for our school and have been spending days optimizing some queries. Currently i have the ff. tables(simplified)
Table miscfees, labfees and unitprice contains the amounts needed to compute for the enrollment fees of a student basing from his program
Table enrolled contains all student currently enrolled for a School Year and Semester
Table reservation contains all reserved courses
Table invoice contains the payments of the students
i do currently have a code for this which uses a cursor(which takes up a lot of code)
can you please help me
create an single SQL statement that will display students who have back accounts: whose total tuition fees (misc fees + labfees + unitprice * totalreservedunits) > sum of credit in invoice
miscfees
mfidmfamtpid yrlevel sy sem
11500 p1 1 2008-20092
21800 p1 2 2008-20092
32000 p2 1 2008-20092
42300 p2 2 2008-20092
unitprice
upidupamtpidyrlevel sy sem
1230 p1 1 2008-20092
2250 p1 2 2008-20092
3180 p2 1 2008-20092
4200 p2 2 2008-20092
labfees
lfidlfamt pidyrlevel sy sem
11500 p1 1 2008-20092
21800 p1 2 2008-20092
32000 p2 1 2008-20092
42300 p2 2 2008-20092
enrolled
sid pid sy sem
080123p1 2008-2009 2
080239p2 2008-2009 2
reservation
ridsidunits sy sem
r10801233 2008-20092
r20801233 2008-20092
r30801235 2008-20092
r40802393 2008-20092
invoice
inidsid credit sy sem
1111080123500 2008-20092
1112080239300 2008-20092
11130801231000 2008-20092
1114080123200 2008-20092
11150801232300 2008-20092
March 13, 2009 at 3:26 am
Asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121602
N 56°04'39.16"
E 12°55'05.25"
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply