We have recently created a SQL script that generates a “report” listing all inventory items filtered by a last used date. This allows you to check to see what inventory you currently have that hasn’t had any activity based on a specific cut-off date.
First, the SQL script will need to be modified to reflect the specific cut-off date you want, how you want it filtered as well as where you want the report to be created to. Here is the link to the script. Once downloaded, extract the .sql file.
SPAK Inv Items with No Activity Script.zip
Change the value in the red box to reflect the specific cut-off date you want to use. If you want to have the report filtered by inventory code, leave the value in the green box as is. However, if you want to filter by Latest Transaction Date, change the value here to 3 so it will show “order by 3 asc;”. The box in blue outlines where exactly you want the report to be generated. If you want to place it in a specific folder, update the file path here to point to that location. If you keep it as the default location, please note that this, by default, is a hidden folder. If you do not initially see the ProgramData folder, you will need to display hidden folders. (https://support.microsoft.com/en-us/help/14201/windows-show-hidden-files) Now save the SQL file.
Open the SQL Utility by going to Start | Programs | ProjectPAK | SQL Utility. There are a couple ways to pull in the script within the SQL Utility. You can either copy the entire script within the SQL file and paste it into the SQL Statements field OR you can go to File | Open and select that .SQL file and it will pull in the script. Click on the black execute button.
You should see the information display in the Results section below, but it will also create a report and place it in the location you outlined (the blue box).
If you happen to change the default location and receive the following message whenever you attempt to execute the script, click OK and close out of the SQL Utility.
Next, go back to the SQL Utility shortcut, right-click on it, and select Run as administrator. This elevates the permissions specifically for this utility and should allow it to execute the script.