Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ianfrei
Starting Member
1 Post |
 Posted - 2009-03-13 : 04:51:52
|
| 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 studentsi 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 mfid mfamt pid yrlevel sy sem 1 1500 p1 1 2008-2009 2 2 1800 p1 2 2008-2009 2 3 2000 p2 1 2008-2009 2 4 2300 p2 2 2008-2009 2 unitprice upid upamt pid yrlevel sy sem 1 230 p1 1 2008-2009 2 2 250 p1 2 2008-2009 2 3 180 p2 1 2008-2009 2 4 200 p2 2 2008-2009 2 labfees lfid lfamt pid yrlevel sy sem 1 1500 p1 1 2008-2009 2 2 1800 p1 2 2008-2009 2 3 2000 p2 1 2008-2009 2 4 2300 p2 2 2008-2009 2 enrolled sid pid sy sem 080123 p1 2008-2009 2 080239 p2 2008-2009 2 reservation rid sid units sy sem r1 080123 3 2008-2009 2 r2 080123 3 2008-2009 2 r3 080123 5 2008-2009 2 r4 080239 3 2008-2009 2 invoice inid sid credit sy sem 1111 080123 500 2008-2009 2 1112 080239 300 2008-2009 2 1113 080123 1000 2008-2009 2 1114 080123 200 2008-2009 2 1115 080123 2300 2008-2009 2 |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-03-13 : 05:24:09
|
| Try this..select (misc fees+ labfees + unitprice) * totalreservedunits from table1,table2.... where <condition> having (fees+ labfees + unitprice) * totalreservedunits > sum of credit in invoiceRegardsSenthil.CWilling to update... |
 |
|
|
|
|
|
Enrollment back accounts query