September 25, 2005 at 9:46 am
SQL 2000 - I need to allocate invoices to payments by account code and currency code
All transactions are in one table and amounts in one field OTHER_AMT (credits being negative numbers) and relate to each other by ACCNT_CODE and CONV_CODE
I need to update the ALLOCATION field with a 1 where the sum of the debits equals the sum of the credits. There can be many credits paying of one debit or one credit paying many debits
Is this possible in TSQL or must i resort to recordsets. Any help/script etc would be greatly appreciated
thanks
September 25, 2005 at 10:32 am
Joe Celko just this month published a solution for inventory which could be adapted to Payment allocations.
See http://www.dbazine.com/ofinterest/oi-articles/celko32
SQL = Scarcely Qualifies as a Language
September 26, 2005 at 5:11 am
Also, an example of the table with a few rows of data and the desired result would be helpful...
/Kenneth
September 26, 2005 at 9:07 am
Hi Kenneth et al
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 hope this explains better as currently this is done manually!!
rgds Adrian
| Accnt_code | Other_Amt | Conv_code | Allocation | Trans_date | |
| STH002 | 99,315.00 | ZWD | 1 | 5/11/2005 | |
| STH002 | 14,897.25 | ZWD | 1 | 5/11/2005 | |
| STH002 | 192,230.00 | ZWD | 1 | 5/26/2005 | |
| STH002 | 28,834.50 | ZWD | 1 | 5/26/2005 | |
| STH002 | 555,000.00 | ZWD | 1 | 4/7/2005 | |
| STH002 | (890,276.75) | ZWD | 1 | 5/26/2005 | |
| STH002 | 356,715.00 | ZWD | 6/28/2005 | ||
| SUB001 | 27,500.00 | ZWD | 1 | 1/31/2005 | |
| SUB001 | 1,500.00 | ZWD | 1 | 2/28/2005 | |
| SUB001 | (29,000.00) | ZWD | 1 | 6/1/2005 | |
| SUB001 | 400,000.00 | ZWD | 1 | 6/30/2005 | |
| SUB001 | (400,000.00) | ZWD | 1 | 6/30/2005 | |
| SVB001 | 690,000.00 | ZWD | 3/8/2005 | ||
| SVB001 | 10,120,000.00 | ZWD | 1 | 3/16/2005 | |
| SVB001 | 600,000.00 | ZWD | 3/16/2005 | ||
| SVB001 | (10,120,000.00) | ZWD | 1 | 3/30/2005 | |
| SVB001 | (2,025,000.00) | ZWD | 4/1/2005 | ||
| SVB001 | 210,000.00 | ZWD | 4/4/2005 | ||
| SVB001 | (2,760,000.00) | ZWD | 1 | 6/30/2005 | |
| SVB001 | 2,760,000.00 | ZWD | 1 | 6/30/2005 |
September 26, 2005 at 9:37 am
update t set Allocation = 1
from transactiontable t
inner join (
select Accnt_code from transactiontable
from transactiontable
group by Accnt_code
having sum(Other_amt) = 0
) a on t.Accnt_code = a.Acctn_code
where Allocation is null or Allocation <> 1
September 26, 2005 at 2:21 pm
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?
September 26, 2005 at 2:45 pm
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.
September 27, 2005 at 12:21 pm
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
Again thankyou but anymore ideas
September 27, 2005 at 1:27 pm
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.
The preliminary SQL code, FWIW:
declare @accounts table (
id int identity not null primary key clustered,
accnt_code char(6) not null,
conv_code char(3) not null)
declare @i int, @accnt char(6), @conv char(3)
insert into @accounts
select distinct accnt_code, conv_code
from activity where allocation is null or allocation <> 1
set @i = SCOPE_IDENTITY()
while @i > 0 begin
select @accnt = accnt_code, @conv = conv_code, @i = @i - 1
from @accounts where id = @i
insert into debits??
select pk??, other_amt from activity
where @accnt = accnt_code and @conv = conv_code and other_amount > 0
insert into credits??
select pk??, other_amt from activity
where @accnt = accnt_code and @conv = conv_code and other_amount < 0
? generate all possible subsets of credits and debits
? if totals match, set corresponding Allocation fields = 1
? repeat until either subset is empty or all subsets have been examined
end
September 28, 2005 at 8:37 am
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
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vw_DebtorAlloc')
DROP VIEW vw_DebtorAlloc
GO
Create view vw_DebtorAlloc as
SELECT W1.ACCNT_CODE,W1.TRANS_DATE,W1.CONV_CODE,
SUM(CASE WHEN W2.TRANS_DATE <= W1.TRANS_DATE
THEN W2.OTHER_AMT ELSE 0 END) as RunningTot
FROM Sales AS W1,
Sales AS W2
WHERE (W1.ACCNT_CODE LIKE 'S%' AND W1.ALLOCATION NOT IN ('A', 'C', 'R', '1')) AND W2.TRANS_DATE <= W1.TRANS_DATE AND W2.ACCNT_CODE = W1.ACCNT_CODE
GROUP BY W1.ACCNT_CODE, W1.CONV_CODE, W1.TRANS_DATE
GO
SELECT S.ACCNT_CODE, S.TRANS_DATE, S.OTHER_AMT, S.ALLOCATION
FROM Staff.dbo.Sales AS S
INNER JOIN
Staff.dbo.vw_DebtorAlloc AS L
ON
S.ACCNT_CODE = L.ACCNT_CODE AND S.TRANS_DATE <= L.TRANS_DATE AND L.RunningTot = 0
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply