SQL Statement to Update ReorderPoint based on QTY Sold During time

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

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

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