SQL Statement

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.

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.

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."

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 Anyone know the SQL statement(s) to delete items from the database that

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.

On Jun 5, 7:19 am, Krista wrote: > Anyone know the SQL statement(s) to delete items from the database that > haven't been sold since a certain date?

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.