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

0
Heather (83)
10/16/2007 2:28:00 AM
pos 14173 articles. 0 followers. Follow

3 Replies
564 Views

Similar Articles

[PageSpeed] 29

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
0
jetspeed (46)
10/16/2007 11:19:01 PM
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


"jetspeed" <jetspeed@discussions.microsoft.com> wrote in message 
news:EE23D4E2-2F3E-41B0-8820-955E0CB63948@microsoft.com...
> 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 

0
marcr (93)
10/17/2007 12:14:14 AM
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



"Heather" <Heather@discussions.microsoft.com> wrote in message 
news:5ED6CA2E-E7A1-42D3-BD34-0E6C01D84A0C@microsoft.com...
>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
> 


0
10/17/2007 2:14:30 AM
Reply:

Similar Artilces:

MFC/VC/ATL/STL Goldmine has been updated: http://preciseinfo.org/Convert/index_Convert_mfc.html
Good news: Site search has been fully implemented. You can search the entire site or collection or a single chapter related to specific context to find what you are looking for. Bad news: We are currently out of sync with google. So, if you do a google search you might see a different article than what google shows. So, it is suggested to search the site instead. First of all it is a much more powerful search than what you can do with Google. Plus you are going to see the right thing. MFC Goldmine collection contains the extensive collection of articles going back several y...

Query-based Distribution Groups Attribute Question
A student asks: When a new attribute is added to a user class object in the schema, does that attribute automatically appear in the choices one has when configuring a query in the definition of a Query-based Distribution Group? If not, how would one get it to appear? Many thanks in advance! JB Fields jbfields@msn.com ...

OWA time out ?
Can the OWA client be configured to time out due to inactivity? Also, how can the default setting of OWA be configure for all clients access ? Thanks, Andrew If you're talking OWA 2003, then yes, use forms-based authentication: http://hellomate.typepad.com/exchange/2003/11/formsbased_auth.html -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "andrewlin@canada.com" <anonymous@discussions.microsoft.com> wrote in message news:04b601c3c966$e2915b90$a001280a@phx.gbl... > Can the OWA client be configured to time out d...

Count Function on True Statements
I wonder if I can do a count on a specific value, like if I had this: CAR1 CAR1 CAR2 CAR2 CAR2 CAR3 Is there a way I can count how many "car2" I have? Currently, the only way I can do this is to set a true/false statement then count the trues. -- krayziez ------------------------------------------------------------------------ krayziez's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34891 View this thread: http://www.excelforum.com/showthread.php?threadid=546334 =countif(a1:a10,"car2") This is a function that accepts wildcards, so if y...

Force a derived class to Serialize/Serialise as its base class
Hi, I'm looking for a way to force a derived class to serialise/serialize as its base class. I was hoping that this was possible by just applying an attribute to the the sub class. public class BaseClass { } // some sort of attribute to force serialization as type of BaseClass [XmlType(typeof(BaseClass))] public class DerivedClass: BaseClass { } The base class is actually generated from XSD so I created a DerivedClass to add a few helper methods. So, I just want the serialization of the base class to take place. Thanks, Phil ...

visual studio and sql server
If I have installed visual studio, does that license me for SQL Server ? In other words, do we have to buy another license for SQL or does SQL come with Visual Studio and hence could be covered with my VS license. Are SQL client tools ( Management studio ) installed on client machines free i.e. not installing the database engine ? To what use do you want to install and use SQL-Server? Some versions of Visual Studio come with a copy of the Developer Edition of SQL-Server. If this is your case, you can then install it but this version can be used only for designing, develo...

An uneven time-increment
Dear all, If in a spreadsheet you have years 1985, 1986, 1987, 1988, 1989, 1990, 2050 with associated data for each year, how do you produce a line graph of this without 2050 being positioned at the same interval on the x-axis as the difference between the other years? To clarify, because the difference between 2050 and 1990 is greater than the difference between the previous years (all 1), how do get Excel to represent this on the x-axis of a line graph? Thanks for your help, Steve -- smurray444 ------------------------------------------------------------------------ smurray444's Pr...

Why is this combo box giving me hard time?
i load my combo box with data in the ressource editor . data: 0;1;2;3. But when i lunch the app. nothing in my Cono box. The combo box is on a dialog box.. Bredal Jensen wrote: > i load my combo box with data in the ressource editor . > > data: 0;1;2;3. > > But when i lunch the app. nothing in my Cono box. > > The combo box is on a dialog box.. > > Are you sure there is nothing? You must call oCombo.SetSel(0); Otherwise the first item is blank. BTW, don't use the editor. Why don;t you rather do oCombo.AddString("0"); oCombo.AddString("...

Suppress msg from update query
I would like to run this update query automatically when the database opens. At present I have it running from a macro when the form is opened however it prompts the user twice before it runs. I would like to eliminate the user input. UPDATE Transactions SET Transactions.Status = "Cleared" WHERE (((Transactions.date)<Date()-10) And ((Transactions.Status)="Pending")); TIA Dim strSQL As String strSQL = "UPDATE Transactions " & _ "SET Transactions.Status = 'Cleared' " & _ "WHERE Transactions.[Date]<Date()-10 " ...

Add the ability to update Kit Components in eConnect
We have the need to update Kits in GP where the original kit was originally setup incorrectly. The current eConnect stored procedure is taCreateKitItemRcd. As you can see from the name, it only creates and does not allow updates if it exists. The message fails. We are going to add some code to our taCreateKitItemRcdPre stored procedure to help us accomplish our goal. We would rather not write or maintain this code, but we will for now. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion...

How do I fix my excel data base?
How do I undo specific colums in an excel data base? Millie Irene wrote: > How do I undo specific colums in an excel data base? How do you mean "undo"? What did you "do" in the first place? -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk Just a tad more detail here would not go amiss :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It...

Last logon time error
I'm using Exch 5.5 . In the server admin page the section server > Private Information stores > mailbox Resources , some user last logon time showing fictitious date .When I check the system date of that machine it showing the correct date , also last access time in the LOGON section showing the correct date / time ...

Import Entities
Hello, I exported all entities that a I have customized or not from my server to import this entities in other server. When I select any entitie or all entities return a message: SQL Error. To obtain more information contact your system administrator. Any idea that i do? Thanks for your help. you may need to run a trace to know for sure, but often this is the case: In environment 1, you have created an attribute (new_height for example) for an entity. In environment 2, you have created an attribute with the same name for the same entity. If you then export the customizations from ei...

SQL Maintenance plan
The SQL DB for our Great Plains was never set up with a maintenance plan. Also, for some strange reason the recovery model is Simple. I'd like to convert to full recovery and have a periodic shrink of the files. Are there any known issues with this, and also are there recommendations for available free space after shrinks, etc? Thanks, Brad Tumer I use fully recovery on all the companies and simple for anything else including the TWO company. I shrink to 10% but it seldom gets that low. If you check the box about moving pages to the beginning of the file you get better res...

Very simple update query running super slow!
I have two tables: Rugman and PDrive The table look like this: Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text) PDrive -- ID (Auto/key) / Field1 (text!) The reason I had to use text for Number and Field1, is because there are a few lines what have a number like: 1234-2 I just want to check the Number in table Rugman and see if it does exist in the table PDrive... If it does: field test must be changed to "done" for that record where the numer exists in the other table... Don't get why it's running so slow. I have created also other update queries ...

Making a query that shows a value based on another value.
Hi I have a Query that extracts two fields of data from a table 1. CustomerName (Contains customer name) 2. BuySell (Has either the Word "Buy" or "Sell") However I wish this query to display CustomerName (As above) but I want it to display a word based on whether field 2 is the word 'Buy' or the word 'Sell'. So for example if the word 'Buy' is in field 2 then I want the word 'RHS' to be displayed and if the word 'Sell' is in field 2 I want the word 'LHS' to be displyed. I do not need this to save into a table jus...

Sending updates to only new or deleted attandees
When adding an attendee to a meeting, and sending the meeting by "Send update only to added or deleted attendees" option all attendees still recieve the updates. Is there a way to fix this? Thanks ...

Can I have in Excel SQL query computed constants like "? as Col1"
Trying to have a query that can set a constant in the select statement, for example: SELECT Author, '11/15/2005' as Updated, PublishDate FROM Author WHERE Author like 'A*' Can I prompt the user for this value as an input, for example: SELECT Author, ? as Updated, PublishDate FROM Author WHERE Author like 'A*' Maybe with a prompt "Please enter Updated date". Before resorting to VBA or Access, just want to see if it can be done in Excel. ...

Updating Links #2
I have an Excel WorkBook called EOH.xls that asks if I want to Update the Links upon its opening. I am opening this WorkBook through a VB application that I wrote but cannot figure out how to open the WorkBook while automatcially choosing 'NO' to Update the Links message. I can write code in another Excel WorkBook to open the EOH.xls WorkBook or by turning off the 'Ask to update links', but I would like to programmatically do so in my existing VB application. Is this possible and if so, how? Any suggestions? Inside xl's VBA, I could do something like: Di...

Return Corresponding Value Based on Comparing Two Sheets of Data
Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ...

date+time input mask
Hi, I have a textbox where I'd like users to input date and time in the format: mm/dd/yyyy hh:mm AM (or PM). My input mask is: 00/00/0000\ 00:00\ >LL The format is General Date. The column this textbox is bound to on the table is date/time with format of General Date. When I input a date and time, I'm getting an error. I input 03/31/2010 01: 15 AM. When I tab off the field, I get the following message: "The value you entered isn't valid for this field". What am I doing wrong? thanks. CLO -- Message posted via AccessMonster.com ht...

Download bank statement
How Can I download on line banking information from non - US bank that have the data available on Line? I am trying to do this with Italian Bank, but the list MS Money 200 offer is only for US institution. Fabio Fontana If the Italian Bank provide downloads, then you might just be able to try it and see whether Money accepts the import. If the bank isn't listed in the US list, it doesn't necessarily mean you can't use it. However, I don't think you'll get any support from MS if things go wrong, so try it in a test file first. -- Glyn Simpson, Microsoft MVP - Money Ch...

update the test company
How we transfer the data from live company to test company? The test was created long time ago by the suppliers of the Great Plains and we want to update weekly. More than that, we have new smartlists created in live company that are not available in test. Any help is welcome. This article has all the info you need... https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYQLYTNSUKZPXKMUNVUVWKNQNQNSKXUQPW&wa=wsignin1.0 "Radu LP" wrote: > How we transfer the data from live company to test company? The test was > created long time ago by the suppliers o...

Office 2003 Service Pack KB907417 update error
System has tried dozens of times to automatically install an IMPORTANT upgrade but fails. I have tried to manually install the upgrade, but it fails. all I get is an error code 57E and even though this error appears to have been reported to Microsoft by many others, I can find no solutions. Tip: Insert your Office 2003 CD into the appropriate drive bay before the next installation attempt. Description of the update for Office 2003 (December 9, 2008) http://support.microsoft.com/kb/949074 You cannot install an Office 2003 service pack http://support.microsoft.com/kb/8842...

Times and Dates
I've noticed that the date fields have a database field type of Date/Time, but under the Formatting tab in Customization only allows a display type of 'Date Only'. Does anyone know if the time is actually being stored? And is there any way to get the time displayed (preferably in an editable format)? I need to track the times of incoming cases to determine if we are meeting our response guarantees. Our guarantees are within hours, not days. Thanks! I believe the times are in the GMT zone. As for displaying what you need. What do you mean? Like in a crystal report? In a cry...