question

ljmack00 avatar image
ljmack00 asked

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.
sql-server-2008ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
1 Like 1 ·
ljmack00 avatar image ljmack00 commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.