Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    16

    Problem identifying records that 'Total' to a certain value?

    Hi folks.

    I have a table that consists of 428 invoice records which have a total value of 477631.78. Now, I have been asked to try and identify a list of those invoices that amount to a total of 120796.27.

    Is there any code/program out there which can be used to help identify all possible combinations of Invoice records that would total 120796.27?

    Thanks in advance,
    Kev

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    yes there are several programs out there which could do this, however none of them is going to do exactly what tyour want out of the box

    SQL is a means of querying the data
    however you may need to use another language as well to go through the permutations

    you mention that you have to find 120K of invoice out of 480k

    is there a reason for the 'magic' number, what is driving the request,
    is it a series of invoices from a company / companies
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2005
    Posts
    16
    Thanks for the swift response.

    We kind of screwed up this weekend in that we didnt run a weekly update program that we were suppposed to. This has resulted in 120796.27 worth of Invoices being allocated a date of 3rd April 2006 instead of 1st April 2006.

    Subsequently, this screws up our datawharehousing system, which is why we are wanting to move the correct Invoices back to a date of 1st April 2006.

    Identifying those Invoice records that need to be moved back is proving to be a bit of a nightmare though.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Let's just imagine that you are a really lucky guy, and the 120796.27 discrepancy is composed of just 4 out of the 428 total invoices. That means you only have to check 33 billion different permutations.

    Now imagine that you are not so lucky...do you feel lucky?

    Now imagine you had made a backup...did you make a backup?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so its an invoicing problem
    is the invoice number sequential? or is there some other method of identyfying the invoice sequence
    is there a date range that works
    what happens if you group by date
    do you have a timestamp on the data
    you jsut be 'lucky' and you data is in the sequence it was written
    try a simple reportadding the value of invocies above + this pone and see if it gets to your magic number
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Good point. If the data is sequential, he does not need to test permutions. He just needs to find the breakpoints that bracket the correct total amount.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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