Getting last few rows depending on a criteria
Here is my sample table.
I am wanting to generate the fee slips for each student. SAMPLE_FEE is actually a view from a table.
What I want from the data is
SID LID LDATE DESCR Amount
---------- ---------- --------- ------------------------- ----------
456 18722 06-SEP-11 2 Month(s) TUITION FEE 1600
458 18707 05-SEP-11 Arrears 1200
464 18418 20-AUG-11 Arrears 600
464 18683 01-SEP-11 1 Month(s) TUITION FEE 1000
466 18696 01-SEP-11 1 Month(s) TUITION FEE 600
OK
I do not want
sid = 417 because it has no due amount (gtotal = 0)
But I want the following for the reasons given as well.
sid = 456 has no arrears but has not paid last two months fee (gtotal = 1600)
sid = 458 has paid some amount so arrears are there (gtotal = 1200)
sid = 464 should print two lines because it has arrears + most recent fee as well
sid = 466 has no arrears but has not paid most recent fee so far.
In simple words, report should print any arrears in one line and all the TUITION FEE after last Payment received in multiples of TUITION FEE. Once a payment is made, if it is full then do not print that sid, if it is partially paid then it should print as arrears.
Thanks for your time.
(The sample script is attached as well)
I am wanting to generate the fee slips for each student. SAMPLE_FEE is actually a view from a table.
What I want from the data is
SID LID LDATE DESCR Amount
---------- ---------- --------- ------------------------- ----------
456 18722 06-SEP-11 2 Month(s) TUITION FEE 1600
458 18707 05-SEP-11 Arrears 1200
464 18418 20-AUG-11 Arrears 600
464 18683 01-SEP-11 1 Month(s) TUITION FEE 1000
466 18696 01-SEP-11 1 Month(s) TUITION FEE 600
OK
I do not want
sid = 417 because it has no due amount (gtotal = 0)
But I want the following for the reasons given as well.
sid = 456 has no arrears but has not paid last two months fee (gtotal = 1600)
sid = 458 has paid some amount so arrears are there (gtotal = 1200)
sid = 464 should print two lines because it has arrears + most recent fee as well
sid = 466 has no arrears but has not paid most recent fee so far.
In simple words, report should print any arrears in one line and all the TUITION FEE after last Payment received in multiples of TUITION FEE. Once a payment is made, if it is full then do not print that sid, if it is partially paid then it should print as arrears.
Thanks for your time.
(The sample script is attached as well)
FEE> SELECT * FROM SAMPLE_FEE;
SID LID LDATE DESCR DB CR GTOTAL
---------- ---------- --------- ------------------------- ---------- ---------- ----------
417 16624 05-APR-11 REGISTERATION FEE 500 500
16625 05-APR-11 TUITION FEE 600 1100
16633 05-APR-11 EXAM FEE 2011 600 1700
16641 05-APR-11 PAYMENT RECEIVED 1700 0
17127 01-MAY-11 TUITION FEE 600 600
17375 01-JUN-11 TUITION FEE 600 1200
17623 01-JUL-11 TUITION FEE 600 1800
17871 01-AUG-11 TUITION FEE 600 2400
17952 05-MAY-11 PAYMENT RECEIVED 2400 0
18639 01-SEP-11 TUITION FEE 600 600
18700 05-SEP-11 PAYMENT RECEIVED 600 0
456 16908 26-APR-11 REGISTERATION FEE 0 0
16909 26-APR-11 TUITION FEE 0 0
16910 26-APR-11 EXAM FEE 600 600
16912 26-APR-11 PAYMENT RECEIVED 800 -200
16913 27-APR-11 PAYMENT RECEIVED 600 -800
17166 01-MAY-11 TUITION FEE 800 0
17414 01-JUN-11 TUITION FEE 800 800
17662 01-JUL-11 TUITION FEE 800 1600
17910 01-AUG-11 TUITION FEE 800 2400
18201 04-JUN-11 PAYMENT RECEIVED 2400 0
18675 01-SEP-11 TUITION FEE 800 800
18722 06-SEP-11 TUITION FEE 800 1600
458 16917 29-APR-11 REGISTERATION FEE 1500 1500
16918 29-APR-11 TUITION FEE 0 1500
16919 29-APR-11 EXAM FEE 600 2100
16920 29-APR-11 PAYMENT RECEIVED 2700 -600
17168 01-MAY-11 TUITION FEE 1200 600
17416 01-JUN-11 TUITION FEE 1200 1800
17664 01-JUL-11 TUITION FEE 1200 3000
17912 01-AUG-11 TUITION FEE 1200 4200
18003 10-MAY-11 PAYMENT RECEIVED 3600 600
18677 01-SEP-11 TUITION FEE 1200 1800
18707 05-SEP-11 PAYMENT RECEIVED 600 1200
464 18237 09-JUN-11 REGISTERATION FEE 3000 3000
18238 09-JUN-11 TUITION FEE 1000 4000
18239 09-JUN-11 EXAM FEE 600 4600
18241 09-JUN-11 PAYMENT RECEIVED 4000 600
18377 01-JUL-11 JUNE JULY FEE 2000 2600
18418 20-AUG-11 PAYMENT RECEIVED 2000 600
18683 01-SEP-11 TUITION FEE 1000 1600
466 18352 13-AUG-11 REGISTERATION FEE 0 0
18353 13-AUG-11 TUITION FEE 0 0
18354 13-AUG-11 EXAM FEE 200 200
18357 13-AUG-11 PAYMENT RECEIVED 200 0
18696 01-SEP-11 TUITION FEE 600 600
46 rows selected.ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or more precisely sql1.sql
36493635 requires 6 accesses to the sample_fee table, that will scale poorly as data volume increases.
http:#36490354 only needs to scan the table once.
http:#36490354 only needs to scan the table once.
ASKER
Millions of thanks
(words can't express my true feelings, just brilliant)
(words can't express my true feelings, just brilliant)
sql1.sql