We sell unique products where reorder level changes every 2-3 months. We want to run a SQL query and update item table with ReorderPoint and ReorderQTY based on QTY sold during specific period.
Please review this SQL Statement
SELECT ItemLookupCode, ReorderPoint, SUM(lastweek.Quantity) as LastWeek, SUM(lastqy.Quantity) as LastQY FROM Item LEFT JOIN TransactionEntry lastweek ON Item.ID = lastweek.ItemID and lastweek.TransactionNumber IN ( SELECT [TransactionNumber] FROM [Transaction] WHERE YEAR([Time]) < 2004) LEFT JOIN TransactionEntry lastqy ON Item.ID = lastqy.ItemID and lastqy.TransactionNumber IN ( SELECT [TransactionNumber] FROM [Transaction] WHERE YEAR([Time]) >= 2004) GROUP BY ItemLookupCode, ReorderPoint
I am using RMS 1.3 with SQL 2005. With this statement I can RUN SQL query and get results but I cant update the table with new results. We want to reset our ReorderPoint and RestockLevel on regular basis. We are in unique industry where reorder point is not fixed. Its changes every two to three months. What I want to accomplish with this statement is run this statement and update the ReorderPoint and RestockLevel in item table based on QTY sold during that period. i.e. Update item table, RestockLevel with QTY sold during Last Month. Then I want to RUN another QUERY to update item table, ReorderPoint withLast two week sale.
Could you please also teach me /give me example of how to use different time period in this SQL statement to get my results. Like how to run this query with Date range, Two weeks, Three Weeks, Last Month, Month of August 2007, etc I want to RUN this Statement OF QTY Sold during specific period then Update ReorderPoint and RestockLevel Item table for specific period