Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Lightbulb Unanswered: Offsetting Records

    Hi,
    Im looking for an way to offset transactions from two tables.

    One table will contain transactions like this.
    ID Prod Amount
    1 a 27
    2 b 35
    3 a 27

    ANd the other will contain any possible negatives (where amount is negative)

    ID Prod Amount
    4 a -27
    5 b -40
    6 a -27

    ID 1 can offset with ID 4, or 6, but most only offset with one of them

    WHat i need to do is to find these pairs, then move both records to another table and continue until i reach the bottom of the second table.

    At the moment i am using a while loop to spin through table 2 and then a query to find a match in table 1, then 4 querys to remove append, then delete the transactions (2 append from 2 tables, then 2 delete from 2 tables)

    I have to write in sql becuase all of the field names are variables. (i.e. user input from form. and can have up to 6 columns, but as few as 1.)

    Any ideas. Would be really grateful.

    efficientcy is important, because it is very slow atm.

    Tom

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SELECT tbl1.id, tbl1.prod, tbl1.amount, tbl2.id, tbl2.prod, tbl2.amount FROM tbl1 INNER JOIN tbl2
    ON abs(tbl1.amount) = abs(tbl2.amount)

    or maybe better:
    ON abs(tbl1.amount) = abs(tbl2.amount) And tbl1.prod = tbl2.prod

    should find all the pairs at once - perhaps to feed a list., once you select a pair from the list you have both IDs for your INSERTs and DELETEs.

    it is never going to be fast because of the slow human part of the process selecting one pair from multiple candidates, then the four queries, then the list-requery. ?? maybe multiselect the list to reduce the number of queries.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    THanks izy, i think i have found a method - i use a query to find all possible matches, then spin through with a while loop making sure that each transaction is only used once.

    Similar to what you suggested.

    Thanks again

    Tom

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by richardst
    THanks izy, i think i have found a method - i use a query to find all possible matches, then spin through with a while loop making sure that each transaction is only used once.

    Similar to what you suggested.

    Thanks again

    Tom
    That won't scale well Tom. How many records you got and what sort of rate of increase is there (transactions per day)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •