Need help writing stored procedure to update invoice numbers w/duplicate records

  • How would I write a query that will look for duplicate records with different bill amounts and then take the record with the greater amount and add a 'C'  to the end of the invoice number(@th_PO) and the record with the lesser amount add an 'R' to the end of that invoice number(@th_PO)?

    Below is what I have created so far.....

    IF EXISTS

    (SELECT*FROM FTP_INV,

    WHERE @th_record_type = 'H'

    COUNT(@th_PO) AS PurchaseOrderDUP

    HAVING ( COUNT(@th_PO) > 1 )

    WHERE < @th_net_amount

    UPDATE FTP_INV

    SET @th_PO = @th_PO + 'C'

    AND

    WHERE  > @th_net_amount

    UPDATE FTP_INV

    SET @th_PO = @th_PO + 'R')

    • This topic was modified 3 years, 12 months ago by  mstamara.morgan. Reason: Clearer description of what is needed
    Attachments:
    You must be logged in to view attached files.
  • I realise that you are new here, but you're going to have to make things clearer than this if you want useful responses.

    First of all, your post does not contain a question. It can be shortened to

    "I want to change some query logic. Here is my attempt so far. The end"

    It's difficult to do anything with that.

    Secondly, phrases such as

    "AND PO is the same"

    "AND  net_amount  IS different"

    mean nothing. The same as what? Different from what?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thank Phil Parkin. I've edited my post, hopefully, it is much clearer than the previous one.

  • How about...

    "I have this table...

    CREATE TABLE SomeData (…);

    which contains data like this...

    INSERT INTO SomeData(f1,f2,f3...) VALUES (1, 2,3),(2,3,4)…;"

    and maybe when I run this query <paste statement here>, I get this result <sample output>, but I want that one <example output>

    Then we can recreate your problem (on a smaller scale) and offer tested solutions.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply