Practical Business Intelligence

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