Thread: SQL Query to show low and out of stock inventory

I'm not a genius at SQL, hopefully someone can assist: I need to write an SQL query to be run from the admin site that will create a custom report that I can access when needed in the custom reports area.

The query needs to show low stock items with quantities under 10. By default, that type of report would also show out of stocks as I need that info also.

What is the exact and complete language to run a report such as that?

It would be great if anyone could pass me the complete needed string - I'm sure it's fairly simple, but I'm not a genius SQL-er.

Thank you.

Do you use complex inventory (eg. your products have Track Inventory by Size and Color set to Yes) or just the default inventory at the product variant level?

We track by default inventory only.

Perfect...you can get all product variant information for items that have less than quantity 10 by using the following query

Code:

select * from dbo.ProductVariant with(NOLOCK) where Inventory < 10

Thank you - how do I turn this query into a custom report that is stored in the reports area? What is added to the query string in order to do that?

Just follow the instructions in our knowledgebase article or the instructions in our manual and use the query I provided in my last post instead of the example query provided in those instructions

Thank you!