Aggregate SQL Query with multiple master-detail tables

Can someone help me from dropping down to procedural code to accomplish the following via SQL? I have 3 tables, here's what my data looks like:

INVOICE TABLE:
PATIENT INVOICE# LOCATION
123 2222 Office

TRANSACTION TABLE:
PATIENT INVOICE# TRANS# SERV_CODE PAY_CODE CHARGE_AMT PAID_AMT

123     2222     1      A             99     200.00       
123     2222     2      B             99     200.00
123     2222     3      C             99     100.00    
123     2222     4                    77                 100.00

LINE_ITEM TABLE:
PATIENT INVOICE# TRANS# LINE# PAID_AMT

123        2222      1        1      100.00
123        2222      1        2      50.00          

The tables are joined on PATIENT, INVOICE#, and TRANS#. What I really want to know is how much we got paid for a particular service code (SERV_CODE). In the example above, TRANS# 1 has payments directly associated with it via the LINE_ITEM table (100.00 and 50.00), but payments for TRANS# 2 and 3 are not posted on the LINE_ITEM; they should share the 100.00 PAID_AMT by a proportion of their CHARGE_AMT in TRANS# 4.

So the end result should look like

PATIENT INVOICE# SERVICE_CODE CHARGE_AMT PAID_AMT

123       2222      A              200.00       150.00
123       2222      B              200.00       66.67
123       2222      C              100.00       33.33

Thanks to anybody who can help me out!

John Received on Thu Mar 06 2003 - 22:44:40 CET