Developing an inventory dataset with SQL Server

Before we can visualize the dataset for the inventory dashboard, we must first create the dataset. The goal of our dataset is to identify inventory stock within different warehouses and determine if a reorder is necessary.

The following query built in SQL Server is an example of a product, Adjustable Race, that has an inventory limit lower than the minimum value required for a reorder:

SELECT  
loc.Name as WarehouseName 
,inv.ProductID 
,prod.Name as ProductName 
,sum(inv.Quantity) as Inventory 
,sum(prod.ReorderPoint) as ReorderPoint 
,case when sum(inv.Quantity) > sum(prod.ReorderPoint) then 'N' else 'Y' end as ReorderFlag 
FROM [AdventureWorks2014].[Production].[Location] as loc 
inner join [AdventureWorks2014].[Production].[ProductInventory] as inv on 
loc.LocationID = inv.LocationID 
inner join [AdventureWorks2014].[Production].[Product] as prod on  
prod.ProductID =  inv.ProductID 
 
where Prod.Name = 'Adjustable Race' 
 
group by  ...