SQL Statement

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Are you sure you don't want to just make inactive if it hasn't been sold by a certain date? This would be the much preferred method. Otherwise you will lose history data in your reports.

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Sure, that does sound like a better option. Can you tell me how to do this?
"Jason" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Try this (backup first):
UPDATE Item SET Inactive = 1 Where LastSold < 'mm/dd/yyyy'
Remember to choose the day AFTER your target date, because you are saying "less than."

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

You didn't specify which product and version you have. I am not sure that the version will matter for a delete but I only have experience with RMS 2.0 and the statements below are for that environment.
SQL is like any other powerful tool. It should be used with caution to avoid doing irreparable damage. Performing large scale deletions can result in the sudden and permanent loss of items that should have stayed. Before doing the deletion you would be well advised to have a good backup and the knowledge how to restore it. I also suggest first using a SELECT that mirrors your planned action to be sure that you get an idea of what items are about to go off to item heaven.
Here is a SELECT statement that lists all the items that have not been sold since the start of this year:
SELECT [Description] ,[LastReceived] ,[LastUpdated] ,[Notes] ,[ID] ,[ItemLookupCode] ,[Price] ,[Cost] ,[LastSold] ,[ExtendedDescription] FROM [Item] Where [LastSold] < '1/1/2007' Order By [LastSold] Desc
I selected a few 'interesting' columns to display. One that bears some consideration is LastUpdated. You might want to have your selection of the items to be terminated take into account that you MIGHT have some items that have not been sold recently but have been active in the database. Things like new items just added to inventory but not yet sold could be deleted if you use just the LastSold column.
Finally, here is the SQL to delete all items not sold this year:
DELETE FROM [Item] Where [LastSold] < '1/1/2007'
I hope this helps. Just be careful out there.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
DON'T DO IT! Do not delete the items. Use Jason's 'Inactive' suggestion.
You can do this with the Inventory Wizard:Task 210 (Make Items Inactive) from Store Operations Manager.
You can set a filter for Last Updated = 5/31/2006 - 1/1/1950 or similar to get everything prior to your chosen date.
The filter for this wizard is, ahem, less than ideal. You cannot set a filter for Quantity <= 0 (to avoid making old stock inactive) and you canot set a filter based on Last Sold (though Last Updated probably works just as well). The inability to filter for Quantity is a major shortcoming and makes the query option better than the wizard IMO.
SELECT ID, ItemLookupcode, Description, LastUpdated, Quantity FROM Item WHERE LastUpdated < 6/30/2006 AND Quantity <= 0
Review the list. Make sure you really want to make all of these items inactive. You can go back and 'reactivate' items if necessary.
BACKUP YOUR DATABASE
UPDATE Item SET Inactive = 1, Quantity = 0 WHERE LastUpdated < 6/30/2006 AND Quantity <= 0
This will make inactive the selected items as well as erase any negative inventory quantities.
BACKUP FIRST!!!
Tom
--
Stop fishing for e-mail


"LakeGator" wrote:
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Great suggestion on setting quantity = 0. I always forget this, but RMS should really make QTY=0 for all inactive items anyway I think.
You brought up a great point about the filters being less than ideal. I often try to add items to a HQ worksheet (like a 250) based on Last Updated Date. I don't understand why you can't enter a TIME because that is how it is in the database. Sometimes hundred of items get updated in a day, but if I want to do a 250 for items that I just updated in the last few minutes, there is no easy way except adding manually.
wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
LakeGator,
If you run/ran that DELETE statement you will not delete all of the info about that Item, Alias references, open transfers and purchase orders, for example, which will cause potential problems in the future
--
Jeff Check Point Software
=====================================================
You must be using Outlook Express/Windows Mail or some other type of newsgroup reader to see and download the file attachment(s). If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right.
http://tinyurl.com/75bgz =====================================================
wrote:<BR>&gt; Anyone know the SQL statement(s) to delete items from the database that<BR>&gt; haven't been sold since a certain date?<BR><BR>You didn't specify which product and version you have.&nbsp; I am not sure<BR>that the version will matter for a delete but I only have experience<BR>with RMS 2.0 and the statements below are for that environment.<BR><BR>SQL is like any other powerful tool.&nbsp; It should be used with caution<BR>to avoid doing irreparable damage.&nbsp; Performing large scale deletions<BR>can result in the sudden and permanent loss of items that should have<BR>stayed.&nbsp; Before doing the deletion you would be well advised to have a<BR>good backup and the knowledge how to restore it.&nbsp; I also suggest first<BR>using a SELECT that mirrors your planned action to be sure that you<BR>get an idea of what items are about to go off to item heaven.<BR><BR>Here is a SELECT statement that lists all the items that have not been<BR>sold since the start of this year:<BR><BR>SELECT [Description]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[LastReceived]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[LastUpdated]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[Notes]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[ID]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[ItemLookupCode]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[Price]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[Cost]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[LastSold]<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,[ExtendedDescription]<BR>&nbsp; FROM [Item]<BR>Where [LastSold] &lt; '1/1/2007'<BR>Order By [LastSold] Desc<BR><BR>I selected a few 'interesting' columns to display.&nbsp; One that bears<BR>some consideration is LastUpdated.&nbsp; You might want to have your<BR>selection of the items to be terminated take into account that you<BR>MIGHT have some items that have not been sold recently but have been<BR>active in the database.&nbsp; Things like new items just added to inventory<BR>but not yet sold could be deleted if you use just the LastSold column.<BR><BR>Finally, here is the SQL to delete all items not sold this year:<BR><BR>DELETE FROM [Item] Where [LastSold] &lt; '1/1/2007'<BR><BR>I hope this helps.&nbsp;&nbsp; Just be careful out there.<BR></BLOCKQUOTE></BODY></HTML>
------=
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.