Dynamics 365 CommunityForums Microsoft Dynamics RMS (Archived) A SQL command for maki...

A SQL command for making items inactive with the following conditions: Onhand = zero and last sold before 1/1/2009

(0) Share

Report

Posted on by Community Member Microsoft Employee

I am looking for a SQL command for making items inactive with the following conditions: Onhand = zero and last sold before 1/1/2009. I need to change the items on both Store Operations and HQ. Thank-you for any help. Adam

*This post is locked for comments

All responses (5)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

Re: A SQL command for making items inactive with the following conditions: Onhand = zero and last sold before 1/1/2009

REally and Truly its just knowing sql.. Look up basic sql commands and you should be able to do pretty much anything. Also there are tools out there that let u connect to the mssql server and see all the tables and run queries. I havent had much/any time to get into this , but i think enterprise manager is one such tool. Good Luck. any questions u can always post here
onontri 100 on at

Like (0)

Report

Re: A SQL command for making items inactive with the following conditions: Onhand = zero and last sold before 1/1/2009

This is fantastic information - Jason, is there any tutorial out there where I can learn how to write other script? I'd like to run similar queries - but more narrow in focus - by vendor; description;...

where can I learn to do this?

thanks you - alfred
Community Member Microsoft Employee on at

Like (0)

Report

Re: A SQL command for making items inactive with the following conditions: Onhand = zero and last sold before 1/1/2009

you need to login to administrator and there is a query tool there
onontri 100 on at

Like (0)

Report

Re: A SQL command for making items inactive with the following conditions: Onhand = zero and last sold before 1/1/2009

Ryan - this may be a stupid question - but where do I run this script?
Verified answer

Ryan Sakry 3,425 on at

Like (0)

Report

Re: A SQL command for making items inactive with the following conditions: Onhand = zero and last sold before 1/1/2009

Adam,

This script will do what you want, though it will not get rid of any items that have never sold as the last updated will be NULL.

UPDATE Item

SET Inactive = 1

WHERE Quantity = 0 and LastSold < '2009-01-01'