SQL query to update annual usage qty in PART_SITE table

tritland

Has anyone gone through the effort to set up a SQL query to update the annual usage quantity in the Part_Site table?

It seems like it should be simple enough, but I am not an SQL expert.

 

Thanks in advance for any help.

Wichtige Lösungen in der Wissensdatenbank Februar 2012.pdf

tritland

This is what I managed to come up with and it seems to work.



UPDATE PART_SITE

SET ANNUAL_USAGE_QTY = TT.QTY

FROM PART_SITE INNER JOIN

(SELECT PART_ID, SITE_ID, SUM(QTY) AS QTY

FROM INVENTORY_TRANS

WHERE (TYPE = 'I') AND (DATEDIFF(MONTH, TRANSACTION_DATE, GETDATE()) <= 12)

GROUP BY PART_ID, SITE_ID) AS TT ON PART_SITE.PART_ID = TT.PART_ID AND PART_SITE.SITE_ID = TT.SITE_ID