I have to update allocation with 1's as u c below. One credit can match many debits or just one debit. There can be more than one credit per account in a match period. There is no other way of matching debit vs credit- they do not share a reference code - it is just matching within an account code

I don't want to confuse the issue but I come from an accounting background and I very seldom saw the payments on invoices match to the penny unless they were made on one payment. Do you have a front-end process to issue a credit/debit memo for small under- or over-payments? If not, you may need to adjust the "=0" to something like "< 1.00 or > 1.00" to account for small differences.

Mr. Coleman's solution is straightfoward and elegant, but I'm guessing it won't do what you need.

The solution works only when all credits in the account equal all debits (as stated in the original post). However the underlying assumption in your original question is that a new invoice won't be posted until the previous invoice is paid in full (i.e. credits = debits). For instance, in the first group (STH002) the algorithm would work if run between 5/27 (after the credit) and before the 6/28 debit was recorded, but would not workif it was run for the first time after 6/28.

To put it simply, the only time the update takes place is when all debits = all credits. In real life, you are probably posting new debits before receiving credits for the existing debits and the two totals will almost never equal one another.

Without document numbers, it seems you are stuck with an open balance method of reporting rather than applying specific credits to specific debits. Is there any way to add another reference field?

It also occurred to me that the problem statement was a gross oversimplification of a full-blown accounting system. It is also stated that this is currently being done manually, so we're not talking about a full accounting system. But the problem, as stated, has a simple solution in T-SQL.

My guess is they only trust the computer to handle the invoices & payments that do balance to the penny, and humans will still have to manually look at the accounts with discrepancies.

Hopefully the day will come when they can buy an accounting system, rather than asking you to build it one piece at a time.

Hi Carl I still have to lookat at the URL as there is serious logic in the examples

Scott and CJohnson - thankyou for the ideas and code, unfortunately it doesnt achieve the result unless the sum of the account is zero

I am using a multicurrency,multilanguage full blown accounting package which rounds to the nearest penny. Basically i am trying to match transactions so that the package will generate realised gains/losses on exchange. I live in a country where inflation is like 300% and moving every day so this is critical. The package will generate gain/loss journals if the sum per currency within an account is zero( ie all open transactions upto the current period sum to 0)

It can also generate journals based on the sum of transactions per allocation code (valid codes 1 to 9) so that is why i am trying to update the allocation field.

However as CJohnson rightly said invoices are posted as they come in. We cannot defer them to the next period (the creditors require prompt payment) so basically never reach a stage of sum = 0 thus we have to do it manually. The credits come in batches from another system once a week. I could amend another field called DUE_DATE - a number field in the form 20050927 - from sql as we r not using its functionality but thats about it

If you want to check whether any subset of the debits exactly equals any subset of the credits (not to mention being off by a penny), I think you may be beyond what I'd be willing to write in T-SQL. I started sketching out an approach, but I realized if you have 10 open credits and 10 open debits there would be over 1,000,000 subset comparisons. And that's just for one account.

I think I would attack this with a VB program on a good workstation, or you might bring your server to its knees. There also might be a published algorithm to reduce the number of subset comparisons, but it would almost certainly require a language other than T-SQL.

After playing with the code from the the link See http://www.dbazine.com/ofinterest/oi-articles/celko32 from Karl Fiderl this is what i ended up doing - not a perfect solution but it does enough to help the cashier. If there r ang glaring issues please respond and If anyone has solutions using vbscript and rowsets I would be interested to see them

This will create a list of Debits and Credits to be allocated. Obviously the final select statement must be changed to an UPDATE. Thanks to all for their suggestions