Allocate receipts to Transactions

Hi there

I am trying to allocated receipts to transactions. 1 receipt can pay multiple transactions and multiple receipts can pay 1 transaction.

I have a transaction table which includes the trans, with a flag OP or CL. And amount Paid. When amount paid is same as Transamount the is CL (Closed), this also inclused the receipts and any credit adjustments.

A seperate table maintains the References.Trans_Ref(Empty at moment, what I Want to populate)

IE the Receipt number to the transactions that is paid off. Rec000001 Trans0001 Rec000001 Trans0002 etc.

I am trying to avoid using a cursor to do this work. Table has around 900 000 records. This is a once off run, but may need to be run a few times at the the end of ETL runs.

In essence this is payment allocation script or SSIS Package. Getting a bit lost / Brain block here. Can some-one assist.?

Thanks Lee.

more ▼

asked Sep 26, 2011 at 12:14 AM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I'm not 100% certain I follow everything you just described, but it sounds like you have correlation between transactions and receipts, a mechanism to match them, right? If so, isn't this a matter of a few joins and maybe a subselect. Basically, one query that aggregates receipt values, that's the sub-select, then use that to join to the transaction table and do a comparison on the total receipts to the transaction amount. You can toss in a case statement to say if it's open or closed. Loops & cursors should not be required.

more ▼

answered Sep 26, 2011 at 03:59 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

The link is only LIFO methodology. There receipts I can identify and the normal transactions. I want to create a table that links the receipts to the transactions that paid them. This is post action as the system did not log this. I am trying not to use cursors. But if did a cursor methods would be as Follow: For the currect receipt find first open transaction, amount on rec - Trans amount, if is 0 then next rec, else get the next open trans. Until rec amount is 0.

Hope this clears it a bit. Thanks Lee.

Sep 26, 2011 at 04:03 AM ljmack00

So there's no data link between a receipt and a transction? A receipt doesn't say what transaction it's for? If not, cursors aren't going to help either. But if you do have a mechanism to match a receipt to a transaction, I'm still standing by aggregating the receipts, grouped by transaction, then join them to the transaction through that link, and compare the aggregate value to the transaction amount. It's pretty straight forward and again, no cursors needed.

Sep 26, 2011 at 04:16 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 26, 2011 at 12:14 AM

Seen: 1006 times

Last Updated: Sep 26, 2011 at 12:14 AM

Copyright 2016 Redgate Software. Privacy Policy