x

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

ljmack00 gravatar image

ljmack00
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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1842
x939

asked: Sep 26, 2011 at 12:14 AM

Seen: 561 times

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