Help with Creation of stored proc

• jvElecenter

  SSCarpal Tunnel

  Points: 4502

  More actions

  January 27, 2004 at 6:40 am

  #106851

  

  Hi hope u can help me !

  I have this problem.

  I have two tables, one containing the orders, the other containing the orderlines. Each orderline can have a status, like EN (open), Pa (partial delivered), SO (closed).

  Each order also can have a status (the same ones). Now when all the orderline of the same order are having status SO, then the status of the order itself should be set to 'SO'. Normally the ERP system should to this, but it doesn't function to well, so i was to adjust it myself by running a stored procedure each night.

  How can i create this ?

  Many thx

  El Jefe

  
  JV

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  January 27, 2004 at 7:47 am

  #491757

  UPDATE o SET Status = 'SO'

  FROM Orders o JOIN

  (SELECT DISTINCT OrderId

   FROM OrderLines l

   WHERE NOT EXISTS

   (SELECT *

    FROM OrderLines

    WHERE OrderId = l.OrderId

    AND Status <> 'SO')) d ON o.OrderId = d.OrderId

  WHERE Status <> 'SO'

  
  
  --Jonathan