Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Matching Debits to Credits Expand / Collapse
Author
Message
Posted Sunday, September 25, 2005 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 9:41 AM
Points: 15, Visits: 63

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




Post #223408
Posted Sunday, September 25, 2005 10:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:23 AM
Points: 2,281, Visits: 4,233
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
Post #223409
Posted Monday, September 26, 2005 5:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

Also, an example of the table with a few rows of data and the desired result would be helpful...

/Kenneth




Post #223495
Posted Monday, September 26, 2005 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 9:41 AM
Points: 15, Visits: 63

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_codeOther_Amt Conv_codeAllocationTrans_date
STH00299,315.00  ZWD15/11/2005
STH00214,897.25 ZWD15/11/2005
STH002192,230.00 ZWD15/26/2005
STH00228,834.50 ZWD15/26/2005
STH002555,000.00 ZWD14/7/2005
STH002(890,276.75)ZWD15/26/2005
STH002356,715.00  ZWD 6/28/2005
SUB00127,500.00  ZWD11/31/2005
SUB0011,500.00 ZWD12/28/2005
SUB001(29,000.00)ZWD16/1/2005
SUB001400,000.00 ZWD16/30/2005
SUB001(400,000.00) ZWD16/30/2005
SVB001690,000.00  ZWD 3/8/2005
SVB00110,120,000.00 ZWD13/16/2005
SVB001600,000.00 ZWD3/16/2005
SVB001(10,120,000.00)ZWD13/30/2005
SVB001(2,025,000.00)ZWD4/1/2005
SVB001210,000.00 ZWD4/4/2005
SVB001(2,760,000.00)ZWD16/30/2005
SVB0012,760,000.00  ZWD16/30/2005




Post #223603
Posted Monday, September 26, 2005 9:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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




Post #223620
Posted Monday, September 26, 2005 2:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 27, 2010 5:17 AM
Points: 104, Visits: 3

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?

Post #223731
Posted Monday, September 26, 2005 2:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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.




Post #223736
Posted Tuesday, September 27, 2005 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 9:41 AM
Points: 15, Visits: 63

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




Post #224064
Posted Tuesday, September 27, 2005 1:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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




Post #224107
Posted Wednesday, September 28, 2005 8:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 9:41 AM
Points: 15, Visits: 63

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




Post #224410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse