Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm still working on the project mentioned here (http://dba.stackexchange.com/questions/2428/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system).

The system is to give the user the option to either pay specific amounts on specific charges, or make generic "you figure it out" payments. Given the table structure we went with (PAYMENTS, CHARGES, PAYMENTS_TO_CHARGES) I'm looking to cheat a bit. Basically I'm looking for an amazingly snazzy "reconcile" SQL query that will do the following:

STEP 1) Grab all Payments with remaining balance (basically credits)

STEP 2) Grab all Charges with remaining balance (partially paid, etc)

STEP 3) Insert portions of payments in the PAYMENTS_TO_CHARGES table until there is no more available credit or there are no more charges.

…I guess technically this isn't reconciliation so much as creating transactional data, but you get the idea.

Steps 1 and 2 are obviously very easy. It's Step 3 that's the killer. If there's no snazzy way to do this in SQL, I suppose I'll go with the old hand-coded step-by-step loop-through-each transaction and post payment_to_charge…just thought I'd ask.

Thanks in advance!

EDIT 1: I've come up with this query to determine which charges have remaining balance, but it's giving me an error saying "Unknown column 'remaining_balance' in 'where clause'":

SELECT 
       charges.*
     , (charges.amount - transactions.total_paid) as remaining_balance 
FROM charges
   , (SELECT 
             charge_id
           , sum(amount) as total_paid 
      FROM payments_to_charges 
      GROUP BY charge_id) as transactions 
WHERE charges.member_id = 123 
  AND charges.id = transactions.charge_id 
  AND remaining_balance > 0 
  AND charges.active_on < NOW()

I'm sure certain items are simply out of order, but I can't figure out what's generally wrong with this particular query. Should I be using HAVING instead of WHERE? Am I missing something else completely obvious?

share|improve this question
    
Remaining balance is not a column. You could reproduce your your logic for creating your remaining balance result. –  Chad Jul 11 '11 at 15:48

1 Answer 1

up vote 3 down vote accepted

You might be able to do the INSERT into Payemnts_To_Charges in one SQL statement, but I'm not sure it would be worth it. It seems like this would be easier to build, debug, and maintain in procedural code. Something like this:

CREATE TABLE Payments (PaymentId Number(10), Amount Number(6,2));
CREATE TABLE Charges  (ChargeID Number(10), Amount Number(6,2));
CREATE TABLE Payments_To_Charges 
   (PaymentID Number(10), ChargeID Number(10), Amount Number(6,2));

INSERT INTO Payments VALUES (1,4);
INSERT INTO Payments VALUES (2,4);

INSERT INTO Charges VALUES (1,2);
INSERT INTO Charges VALUES (2,5);
INSERT INTO Charges VALUES (3,6);
INSERT INTO Charges VALUES (4,4);
INSERT INTO Charges VALUES (5,10);

Declare
   vPaymentAmount Payments.Amount%Type;
   vAppliedAmount Payments_To_Charges.Amount%Type;
Begin
   For vPayment In (SELECT PaymentID, Amount FROM Payments) Loop
      vPaymentAmount := vPayment.Amount;      
      For vCharge In (
            SELECT ChargeID, Amount FROM
            (
               SELECT ChargeID, Amount - 
                  NVL((SELECT SUM(Amount) FROM Payments_To_Charges pc 
                       WHERE pc.ChargeID = c.ChargeID),0) Amount
               FROM Charges c            
            ) WHERE Amount > 0
         ) Loop
         vAppliedAmount := LEAST(vPaymentAmount, vCharge.Amount);
         INSERT INTO Payments_To_Charges (PaymentID, ChargeID, Amount)
            VALUES (vPayment.PaymentID, vCharge.ChargeID, vAppliedAmount);
         vPaymentAmount := vPaymentAmount - vAppliedAmount;
         If (vPaymentAmount = 0) Then
            Exit;
         End If;
      End Loop;
   End Loop;
End;
/

SELECT * FROM Payments_To_Charges;

Update:

remaining_balance is the issue. You can't reference the aliased value in the WHERE clause. You could either make the query a sub-query and add that condition on a higher level or change remaining_balance in the WHERE clause for (charges.amount - transactions.total_paid).

share|improve this answer
    
Yeah, actually the only table I'll need to update is the P_T_C table. The others are simply static "bulk" data. The goal is simply to see which payments get applied to which charges. It wouldn't be so bad if it were simply a matter of debits and credits. However, they want to support generic payments as well as allow the user to pay specific charges out of order -- hence the P_T_C table in general. –  humble_coder Jun 22 '11 at 0:35
    
...Obviously if they specify the charge, it's an easy situation. However, when they simply apply a generic payment (or if there is credit the next time a charge is added) I need a "reconcile" method to automatically sort out each P_T_C as necessary. –  humble_coder Jun 22 '11 at 0:35
    
Please see EDIT 1 –  humble_coder Jul 10 '11 at 21:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.