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.?
asked Sep 26, 2011 at 12:14 AM in Default
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.
answered Sep 26, 2011 at 03:59 AM
Grant Fritchey ♦♦