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.
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.