SQL Statement to Update ReorderPoint based on QTY Sold During time

I am using RMS 1.3 with SQL 2005 SQL Statement to Update ReorderPoint based on QTY Sold During certin time frame. 2weeks, 3weeks, Month, 2 Month
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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Totally agree
It's an absolute disgrace that Microsoft sell a 'POS' system that can't automatically recalculate order points based on the rate of sale. Our 20 year old DOS system did this easily
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
AMEN! Our old DOS system had a fantastic weighted history algorithm for ordering that worked like a charm and rarely needed any manual intervention. RMS should definitely have an option to recalculate restock/reorder points - after all, isn't a major goal of POS software to use a PC's computing power to keep efficient inventory levels?
Marc

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Heather, First step first, if you are having difficulty updating the table in the same query where you are making these left joins, use a temporary table variable. I created an example below with a table called @lastWeekList (starts with the declare statement.) As you can see it creates the same result set as your query. But at the end, you can use this to make a new join with the item table and make your updates.
Table variables are created and manipulated in memory. Therefore they have some advantages over temporary tables created using the #tableName signature. In this case I simply suggest the @ kind to cut the long story short.
For the date ranges just use two dateTime variables as parameters. You can enter a begining and ending date and run your process. If you cannot put that together, post another question, I will try to help. Let me know if this helps.
declare @lastWeekList table(itemLookupCode nvarchar(25), reorderpoint float, lastWeek float, lastqy float)
insert into @lastweeklist (itemLookupCode , reorderpoint , lastWeek, lastqy )
--YOUR ORIGINAL QUERY BEGINS HERE
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
---END OF YOUR ORIGINAL QUERY
select * from @lastWeekList
UPDATE item
set --your update statement here
from item i, @lastWeekList l
where i.itemLookUpcode = l.itemlookupcode

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here. All logos and trade names are the property of their respective owners.

Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.