Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001

    Unanswered: Matching Debits and Credits query

    I have a SQL statement that matches credit amounts to debit amounts which works fine except in cases when there is more than one debit amount for the same value, the code then applies the 1 credit to all the debits of the same amount.

    eg. if I have the following

    Amount Inv. No.
    $200.00 345
    $300.00 567
    $200.00 129

    In the above example using the code below, if I have a credit of $200.00 it will be applied to both inv nos. 345 and 129. I would just like it applied to either 345 OR 129.

    SELECT Pos.osamt, Pos.ClientNo, Pos.FirstName, Pos.LastName, Pos.Address1, Pos.Address2, Pos.City, Pos.Country, Pos.TransDate, Pos.InvoiceNo, Pos.PolicyNo, Pos.TransAmt, Pos.TaxAmt, Pos.ReceiptAmt, Pos.Currency
    FROM Pos LEFT JOIN Neg ON (Pos.osamt = abs(Neg.osamt)) AND (Pos.ClientNo = Neg.ClientNo)
    WHERE (((Neg.osamt) Is Null));

    Any help would be appreciated.

  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL

    Thumbs down

    Your design is extremely flawed, matching debits to credits by amount only can result in applying the incorrect credit/debit combination even if the client# is the same.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2001
    That's only part of it. I just need it resolved.

  4. #4
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    Quote Originally Posted by toukey1
    I would just like it applied to either 345 OR 129.
    Well, it is YOU who has to know whether it will be 345 or 129. What happens when there's another Inv. no with the same credit ($200)? And another?

    There, of course, is a way - you could, for example, choose MIN(inv_no) or MAX(inv_no) or ...

    Data model is awful; you'd better change it (if you can), because this promises you only sweat, blood and tears.

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    ...just out of curiosity how do you propose to match a payment against an invoice where the payer is disputing part of the invoice (ie they are paying some, but not all of the invoice), or where the payer makes a single payment covering many invoices?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts