This seems like it should have some sort of iterative solution, but I'm a bit stumped.

I have a database that handles registrations and fees for courses, with multiple fees possible for each individual course. I need to find out how much revenue a "non-resident" fee generated above and beyond normal registrations, but it is not possible to count all registrations, because some courses are zero-dollar courses (registrations are for performance measures only) and some courses are the same price regardless of residency status. I can use the application frontend to only select accounts of type "non-resident", but I need to then compare the fee charged with the possible fees to determine if there is a difference, and if there is a difference, to sum the difference over all registrations. Here's my first pass at a design with relevant table fields:

===Registration===
registraion_id (pk)
course_id (fk)
course_fee_amount (actual amount charged)


===Course_Fee_Available===
course_id (pk, fk)
course_fee_seq (pk)
amount
resident_type (1 = resident, 2 = nonresident)

My cases are as follows:

1. A course with the same fee for resident vs. non-resident

The difference of the fee charged and the fee for resident_type = 1 in the course table is the same, sum += 0

2. A course with different fees for resident vs. non-resident

the difference of the feecharged is not zero. sum += difference_of_fees

Data entry policy states that fees are entered in a certain way so that I can (with best case assumptions.... ) count on the odd sequence counters being resident fees and the even sequence counters being non-resident fees. The comparison should always be the fee "below" (ex. course_fee_seq 2 should be a "nonresident" fee and course_fee_seq 1 should be the corresponding resident fee).

I can get a bluk number of the total revenue of all non-resident registrations, but I can't seem to get the appropriate "sum" of what the registrant fees would be if all were residents. I'm sure I'm missing something easy, but I don't work with SQL that often and any assistance would be appreciated.

I also have access to crystal reports if that would make things easier.

Thanks in advance for the assistance!