Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Red face Unanswered: Cancelling purchase orders

    Hi Guys

    Im using SQL server 2005 and I need a script that will cancel purchase orders that are older than 2 months. The script should also update the stock ordered.

    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE purchaseorders
       SET status = 'cancelled'
         , stockordered = 0
     WHERE podate < DATEADD(mm,-2,GETDATE())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    5

    Red face cancelling purchase

    Hi

    Thanks for the code but my scenerio is as folllows:
    when a purchase order is entered into the system it also updates the stock file as ordered stock. This means that when the code cancels the orders table the stock table also need to be updated. Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rajarndenda View Post
    Thanks for the code but my scenerio is as folllows:
    ask yourself how much of your table design you revealed in your first post

    it's still somewhat mysterious, so i don't want to guess again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2010
    Posts
    5

    Smile cancelling purchase orders

    my tables have the following fields:
    ORDER
    (ordtranid)
    (reference#)
    (stockcode)
    (stockqty)
    (date1)
    (status)
    (plucode#)

    STKSOH
    (stockcode)
    (plucode#)
    (store#)
    (stockqnty)
    (onorder)
    (min)
    (max)
    (dateedit)

    The update query you gave me works well and can you assist me with query updating the STKSOH table. I am sorry about the less information i gave in the first time. Please help me. Thank you

  6. #6
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Is this homework? Be honest now as this seems pretty basic.
    -- You can't be late until you show up.

  7. #7
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    may be it is home work in initial such simple questions will arise so professionals such like you had to help us for achieving the work Mr.tosscrosby
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mathukumali View Post
    may be it is home work in initial such simple questions will arise so professionals such like you had to help us for achieving the work Mr.tosscrosby
    Um, huh???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Sep 2010
    Posts
    5

    Cancelling orders

    Guys its not homework. Im trying to create a night run script that cancel outstanding orders older than 2 months

Posting Permissions

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