Analyze Supply Chain Stockout Risk

Use SQL Server to rank stockout impact and supplier delivery risk.

Introduction

30 Second Summary

A warehouse can look fully stocked today while last month's shortages stay buried in old records. Decisions based on today's balance alone can miss the products that repeatedly put revenue at risk.

In this project, you will build a local supply-chain risk analysis with SQL Server and run it from Visual Studio Code. Your report ranks historical product risk beside supplier delivery performance without treating association as proof of causation.

What You'll Build

You run your saved SQL analysis to watch a healthy current snapshot give way to a product-risk leaderboard beside a supplier-performance ranking.

By the end of this project, you'll have:

  • A connected seven-table supply-chain model you can query to show six positive current balances while earlier zero-stock snapshots remain visible in the history.
  • A stockout-impact leaderboard that ranks Barcode Labels first with three stockout snapshots and $2,880.00 in estimated lost revenue.
  • A supplier-performance comparison that separates actual lead time from delivery delay so you can discuss risk without blaming suppliers prematurely.
  • Secret Mission: Turn the retrospective report into a current restocking watchlist that labels products for action and suggests project-defined order quantities.

Are there any prerequisites?

You need basic SQL knowledge. Your computer must run a 64-bit edition of Windows 10 or Windows 11. The installation also requires administrator access plus at least 6 GB available on the system drive.

Before We Start

Before any SQL work begins, this step locks in the business decision your analysis needs to support. The goal is to help a supply-chain decision-maker reduce stockout-related revenue risk without treating every stockout as supplier-caused.

Set Up Your Local SQL Workspace

Your business purpose is set. The report should help a supply-chain decision-maker reduce stockout-related revenue risk without blaming suppliers prematurely.

A local SQL Server 2025 Express engine gives the analysis somewhere to store relational data.

Visual Studio Code becomes your query editor when you add the MSSQL extension.

In this step, get ready to:
  • Confirm that your computer supports the local SQL workspace.
  • Install SQL Server 2025 Express and Visual Studio Code 1.138.
  • Connect through the MSSQL extension and run a version query.
Confirm Windows compatibility and install SQL Server

SQL Server 2025 Express requires a supported 64-bit Windows computer. The installer also needs at least 6 GB of available space on the system drive.

  • Right-click Start on the Windows taskbar.
  • Select Settings.
  • Select System.
  • Select About.
  • Confirm that Edition shows Windows 10 or Windows 11.
  • Confirm that System type shows 64-bit operating system, x64-based processor.

Those two values confirm that the operating system can run the database engine. Storage is the final compatibility check.

  • Return to the System page in Windows Settings.
  • Select Storage.
  • Confirm that the system drive has at least 6 GB available.

Compatibility Check Failed?

  • Pause the installation if Windows reports a 32-bit operating system.
  • Free enough system-drive space if less than 6 GB is available.
  • Return to the compatibility checks before continuing.

The database engine is version-pinned to SQL Server 2025 Express. Your installation path depends on what is already present on the computer.

✔️ I have SQL Server 2025 Express

Your existing database engine meets the version requirement. The connection check later in this step confirms that its named instance is ready.

ⓧ I have an older SQL Server Express

  • Open Microsoft's SQL Server installation guide.
  • Upgrade the existing Express installation to SQL Server 2025.
  • Keep the named instance as SQLEXPRESS.
  • Add your current Windows user as a SQL Server administrator during setup.

ⓧ SQL Server Express is not installed

The installer changes a local Windows service. The administrator prompt authorizes that change on your computer.

The progress screens can sit quietly while Windows configures the service. That pause is part of the installation.

  • Download SQL Server 2025 Express from the Microsoft SQL Server downloads page.
  • Run the downloaded installer as an administrator.
  • Select New SQL Server standalone installation or add features to an existing installation in the SQL Server Installation Center.
  • Select Database Engine Services on the Feature Selection page.
  • Set Named instance to SQLEXPRESS on the Instance Configuration page.
  • Select Windows Authentication on the Database Engine Configuration page.
  • Select Add Current User.
  • Select Install on the Ready to Install page.

Good progress. SQL Server setup should now report that Database Engine Services installed successfully.

Why These SQL Server Choices?

Database Engine Services provides the process that stores data and executes queries. The named instance SQLEXPRESS gives that process a predictable local address.

Windows Authentication uses your current Windows account. Adding the current user during setup grants that account permission to administer this local instance.

SQL Server Installation Stopped?

  • Confirm that the installer is running with administrator permission.
  • Recheck the Windows edition if setup reports an operating-system compatibility problem.
  • Recheck the available system-drive space if setup cannot continue.

Ask for targeted help with the installer details you can see: Help me diagnose my SQL Server 2025 Express installation problem.

Install Visual Studio Code and the MSSQL extension

Visual Studio Code provides the editor window for your SQL. The MSSQL extension adds database connections and a Results pane.

The editor is pinned to version 1.138 for this project. Choose the path that matches your computer.

✔️ I have Visual Studio Code 1.138

Your editor already matches the project version. Continue with the MSSQL extension setup below.

ⓧ I have an older version

  • Open the official Visual Studio Code 1.138 release page.
  • Download the Windows User setup for version 1.138.
  • Run the downloaded installer to update Visual Studio Code.
  • Complete the installer with its default options.

You should now have Visual Studio Code version 1.138 installed.

ⓧ Visual Studio Code is not installed

  • Open the official Visual Studio Code 1.138 release page.
  • Download the Windows User setup for version 1.138.
  • Run the downloaded installer.
  • Complete the installer with its default options.

You should now have Visual Studio Code version 1.138 installed.

  • Press the Windows key to open the search bar.
  • Type Visual Studio Code.
  • Press Enter to open the editor.
  • Press Ctrl+Shift+X to open the Extensions view.
  • Search for mssql.
  • Select SQL Server (mssql) from Microsoft.
  • Read the version shown on the extension details page.

The extension is pinned to version 1.45.1. Its installation path depends on the version shown in the details page.

✔️ I see version 1.45.1

The required MSSQL extension version is already installed. Its MSSQL icon should be visible in the Activity Bar.

ⓧ I see an older version

  • Right-click the SQL Server (mssql) extension tile.
  • Select Install Another Version.
  • Select version 1.45.1 from the available list.

The extension details page should now show version 1.45.1.

ⓧ The extension is not installed

  • Select Install on the extension details page.

The extension details page should now show version 1.45.1.

That completes the editor setup. You should now see the MSSQL icon in the Activity Bar with a Connections view available.

MSSQL Icon Missing?

  • Confirm that SQL Server (mssql) appears in the Installed section of the Extensions view.
  • Restart Visual Studio Code if the extension finished installing without adding its icon.
  • Reopen the Extensions view with Ctrl+Shift+X if the extension page closed.

Ask for help with the state shown in your Extensions view: Help me find the MSSQL extension in Visual Studio Code.

Connect to SQLEXPRESS and verify the engine

A successful connection proves that the editor can reach the local database engine. Windows Authentication lets the connection use the account that setup added as an administrator.

  • Select the MSSQL icon in the Activity Bar.
  • Select Add Connection in the Connections view.
  • Keep Parameters as the connection input type.
  • Enter .\SQLEXPRESS in Server name.
  • Select Windows Authentication as the authentication type.
  • Leave Database empty.
  • Select Connect.

You should see .\SQLEXPRESS as a connected server in the Connections view.

  • Right-click .\SQLEXPRESS in the Connections view.
  • Select New Query.
  • Add the version check to the query editor by pasting this SQL:
SELECT @@VERSION AS [SQL Server Version];

What Does This Query Do?

  • The @@VERSION value asks the connected engine for its build and system information.
  • The SQL Server Version alias gives the returned column a readable heading.

Before you execute the query, which SQL Server version family do you expect the local instance to report?

  • Execute the query by pressing Ctrl+Shift+E.

You should see one row under SQL Server Version in the Results pane. It should contain SQL Server 2025 Express build information.

You have a working local query loop. Visual Studio Code can now send SQL to .\SQLEXPRESS and display the response.

Connection or Query Failed?

  • Confirm that the server name is exactly .\SQLEXPRESS.
  • Open SQL Server Configuration Manager if the local instance does not respond.
  • Confirm that SQL Server (SQLEXPRESS) is running.
  • Reconnect with Windows Authentication if the editor is disconnected.

Ask for help with the connection details and visible result: Help me troubleshoot my local SQLEXPRESS connection.

Your local SQL workspace is ready. Next, you will create the supply-chain database and load the operational data that powers the analysis.

Create and Seed the Supply Chain Database

Your SQL Server workspace in Visual Studio Code is ready. The MSSQL extension can now turn the empty server into a queryable supply-chain dataset.

A connected relational dataset makes every later KPI traceable to source records. It also gives you an early portfolio artifact that already runs.

In this step, get ready to:
  • Create the SupplyChainPortfolio database from a saved SQL file.
  • Build seven related tables with primary keys, foreign keys, and check constraints.
  • Seed the tables with operational data and verify every row count.
Create the database file

A saved creation script gives you a repeatable record of the database setup. The Documents\supply-chain-stockout-analysis folder keeps both project scripts together.

  • In the query editor from earlier, click File in the top menu.
  • Select Save As.
  • Select Documents in the save dialog.
  • Create a folder named supply-chain-stockout-analysis using the New folder control.
  • Enter the new supply-chain-stockout-analysis folder.
  • Enter 00_create_database.sql in the File name field.
  • Click Save.
  • Replace the existing version query by pasting this statement:
CREATE DATABASE SupplyChainPortfolio;

What does this statement do?

  • The CREATE DATABASE statement asks SQL Server to create a new local database.
  • The name SupplyChainPortfolio gives every later script a consistent database target.
  • Save 00_create_database.sql.
  • Press Ctrl+Shift+E to execute the statement.

Good start. You now have the database container that holds the entire supply-chain model.

Database creation did not complete?

  • Confirm that the query editor is still connected to .\SQLEXPRESS.
  • Check that SupplyChainPortfolio appears only once in the statement.

Ask for help with the exact result you see: Help me diagnose why my SQL Server database creation statement did not complete.

  • Refresh .\SQLEXPRESS in the MSSQL Connections view.
  • Expand .\SQLEXPRESS to confirm that SupplyChainPortfolio is listed.
  • Select SupplyChainPortfolio from the query editor's database selector.

✔️ Awesome, I've got everything!

Your creation script is saved. Confirm that SupplyChainPortfolio is the active database before continuing.

ⓧ I'd like to double check the full code

CREATE DATABASE SupplyChainPortfolio;

How to use this reference

Compare this statement with 00_create_database.sql character by character. The file creates the database used by every remaining script.

Build the relational schema

The seven tables separate suppliers, products, warehouses, inventory, purchasing, and sales into connected records. Their constraints protect the relationships that later analysis depends on.

  • Click File in the top menu.
  • Select New Text File.
  • Click File again.
  • Select Save As.
  • Enter the existing Documents\supply-chain-stockout-analysis folder.
  • Enter 01_schema_and_seed.sql in the File name field.
  • Click Save.
  • Create the supplier table by pasting this code into 01_schema_and_seed.sql.
USE SupplyChainPortfolio;

CREATE TABLE dbo.Suppliers
(
    supplier_id INT NOT NULL PRIMARY KEY,
    supplier_name NVARCHAR(100) NOT NULL
);

What does the supplier table protect?

  • The USE statement directs this script to SupplyChainPortfolio.
  • The supplier_id primary key gives each supplier one unique identifier.
  • The required supplier_name keeps every supplier record identifiable.
  • Save 01_schema_and_seed.sql.
  • Select only the code you just added.
  • Press Ctrl+Shift+E to create dbo.Suppliers.

You'll see the selected statement complete successfully. The first table is now ready to receive supplier records.

Supplier table did not appear?

  • Confirm that your selection includes USE SupplyChainPortfolio;.
  • Check that the closing parenthesis remains above the final semicolon.

Get targeted help here: Help me find the problem in my Suppliers table statement.

  • Add the product table on a blank line at the end of 01_schema_and_seed.sql by pasting this code:
CREATE TABLE dbo.Products
(
    product_id INT NOT NULL PRIMARY KEY,
    product_name NVARCHAR(100) NOT NULL,
    supplier_id INT NOT NULL,
    unit_cost DECIMAL(10, 2) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT FK_Products_Suppliers
        FOREIGN KEY (supplier_id) REFERENCES dbo.Suppliers(supplier_id)
);

How are products connected?

  • The product_id primary key identifies each product.
  • The FK_Products_Suppliers foreign key requires every product to reference an existing supplier.
  • The decimal columns preserve two places for product costs and selling prices.
  • Save 01_schema_and_seed.sql.
  • Select only the new CREATE TABLE dbo.Products statement.
  • Press Ctrl+Shift+E to create the product table.

The product table now accepts records tied to valid suppliers.

Product table creation failed?

  • Confirm that dbo.Suppliers was created first.
  • Compare the spelling of supplier_id in both tables.

Use this prompt for a closer look: Help me diagnose the Products foreign key.

  • Add the warehouse table on a blank line by pasting this code:
CREATE TABLE dbo.Warehouses
(
    warehouse_id INT NOT NULL PRIMARY KEY,
    warehouse_name NVARCHAR(100) NOT NULL
);

What does the warehouse table hold?

  • The warehouse_id primary key distinguishes each location.
  • The required warehouse_name gives each location a readable name.
  • Save 01_schema_and_seed.sql.
  • Select only the new warehouse statement.
  • Press Ctrl+Shift+E to create dbo.Warehouses.

The database can now identify the physical location behind each inventory or sales record.

Warehouse statement did not complete?

  • Check that both column definitions remain inside the parentheses.
  • Confirm that the selected statement ends with a semicolon.

Ask for focused help: Help me check my Warehouses table statement.

  • Add the historical inventory table on a blank line by pasting this code:
CREATE TABLE dbo.InventorySnapshots
(
    snapshot_date DATE NOT NULL,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity_on_hand INT NOT NULL,
    reorder_level INT NOT NULL,
    CONSTRAINT PK_InventorySnapshots
        PRIMARY KEY (snapshot_date, warehouse_id, product_id),
    CONSTRAINT FK_InventorySnapshots_Warehouses
        FOREIGN KEY (warehouse_id) REFERENCES dbo.Warehouses(warehouse_id),
    CONSTRAINT FK_InventorySnapshots_Products
        FOREIGN KEY (product_id) REFERENCES dbo.Products(product_id),
    CONSTRAINT CK_InventorySnapshots_Quantity
        CHECK (quantity_on_hand >= 0 AND reorder_level >= 0)
);

Why use inventory snapshots?

  • The composite primary key permits one record for each date, warehouse, and product combination.
  • The foreign keys connect every snapshot to known products and warehouses.
  • The check constraint blocks negative inventory quantities and reorder levels.
  • Save 01_schema_and_seed.sql.
  • Select only the CREATE TABLE dbo.InventorySnapshots statement.
  • Press Ctrl+Shift+E to create the snapshot table.

The model can now preserve inventory history across multiple observation dates.

Inventory snapshot table failed?

  • Confirm that the product and warehouse tables already exist.
  • Compare all three composite primary-key columns with their column definitions.

Get help with the relationships: Help me diagnose my InventorySnapshots constraints.

  • Add the purchase-order table on a blank line by pasting this code:
CREATE TABLE dbo.PurchaseOrders
(
    purchase_order_id INT NOT NULL PRIMARY KEY,
    supplier_id INT NOT NULL,
    order_date DATE NOT NULL,
    promised_delivery_date DATE NOT NULL,
    received_date DATE NULL,
    CONSTRAINT FK_PurchaseOrders_Suppliers
        FOREIGN KEY (supplier_id) REFERENCES dbo.Suppliers(supplier_id),
    CONSTRAINT CK_PurchaseOrders_Dates
        CHECK
        (
            promised_delivery_date >= order_date
            AND (received_date IS NULL OR received_date >= order_date)
        )
);

How does this protect delivery dates?

  • The supplier foreign key links every purchase order to a known supplier.
  • The date check keeps promised delivery dates on or after order dates.
  • The same check allows an unreceived order to keep a null receipt date.
  • Save 01_schema_and_seed.sql.
  • Select only the purchase-order table statement.
  • Press Ctrl+Shift+E to create dbo.PurchaseOrders.

Purchase orders can now preserve promised dates beside actual receipt dates.

Purchase-order table failed?

  • Confirm that dbo.Suppliers exists.
  • Check the parentheses around the date constraint.

Ask for help with this constraint: Help me diagnose the PurchaseOrders date check.

  • Add the sales-order table on a blank line by pasting this code:
CREATE TABLE dbo.SalesOrders
(
    sales_order_id INT NOT NULL PRIMARY KEY,
    order_date DATE NOT NULL,
    warehouse_id INT NOT NULL,
    CONSTRAINT FK_SalesOrders_Warehouses
        FOREIGN KEY (warehouse_id) REFERENCES dbo.Warehouses(warehouse_id)
);

How are sales orders located?

  • The sales_order_id primary key identifies each customer order.
  • The warehouse foreign key records which location handled that order.
  • Save 01_schema_and_seed.sql.
  • Select only the sales-order table statement.
  • Press Ctrl+Shift+E to create dbo.SalesOrders.

Sales orders now carry both an order date and a valid warehouse location.

Sales-order table failed?

  • Confirm that dbo.Warehouses was created earlier.
  • Compare the spelling of warehouse_id in both tables.

Use this prompt to inspect the relationship: Help me diagnose the SalesOrders warehouse foreign key.

  • Add the sales-order-line table on a blank line by pasting this code:
CREATE TABLE dbo.SalesOrderLines
(
    sales_order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity_ordered INT NOT NULL,
    quantity_fulfilled INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    unfulfilled_reason NVARCHAR(20) NULL,
    CONSTRAINT PK_SalesOrderLines
        PRIMARY KEY (sales_order_id, product_id),
    CONSTRAINT FK_SalesOrderLines_SalesOrders
        FOREIGN KEY (sales_order_id) REFERENCES dbo.SalesOrders(sales_order_id),
    CONSTRAINT FK_SalesOrderLines_Products
        FOREIGN KEY (product_id) REFERENCES dbo.Products(product_id),
    CONSTRAINT CK_SalesOrderLines_Quantities
        CHECK
        (
            quantity_ordered > 0
            AND quantity_fulfilled BETWEEN 0 AND quantity_ordered
        ),
    CONSTRAINT CK_SalesOrderLines_Reason
        CHECK
        (
            unfulfilled_reason IN (N'Stockout', N'Other')
            OR unfulfilled_reason IS NULL
        )
);

How do sales lines preserve demand?

  • The composite primary key permits one line for each product on a sales order.
  • The foreign keys connect each line to valid orders and products.
  • The quantity check prevents fulfilled units from exceeding ordered units.
  • The reason check limits unfulfilled demand to the project categories or a null value.
  • Save 01_schema_and_seed.sql.
  • Select only the sales-order-line table statement.
  • Press Ctrl+Shift+E to create dbo.SalesOrderLines.

All seven tables now exist. Their keys make each later metric traceable across inventory, purchasing, and sales.

Sales-order-line table failed?

  • Confirm that both dbo.SalesOrders and dbo.Products exist.
  • Check every parenthesis in the two check constraints.

Get help with the final table: Help me diagnose my SalesOrderLines constraints.

Seed and verify the database

The schema defines what the database can hold. Seed records now give each relationship enough history to support the stockout and supplier-risk analysis.

  • Add four supplier records on a blank line by pasting this code:
INSERT INTO dbo.Suppliers (supplier_id, supplier_name)
VALUES
    (1, N'Apex Components'),
    (2, N'Northstar Goods'),
    (3, N'BlueRiver Supply'),
    (4, N'Summit Industrial');

What does this seed data establish?

  • These four rows establish the complete supplier list used by products and purchase orders.
  • Their numeric identifiers become the shared keys between related tables.
  • Save 01_schema_and_seed.sql.
  • Select only the supplier insert statement.
  • Press Ctrl+Shift+E to insert the supplier records.

The supplier table now contains four records.

Supplier rows were not inserted?

  • Confirm that dbo.Suppliers exists in SupplyChainPortfolio.
  • Check that each supplier identifier appears only once.

Ask for seed-data help: Help me diagnose the Suppliers insert statement.

  • Add six product records on a blank line by pasting this code:
INSERT INTO dbo.Products
    (product_id, product_name, supplier_id, unit_cost, unit_price)
VALUES
    (101, N'Wireless Scanner', 1, 45.00, 79.00),
    (102, N'Packing Tape', 2, 2.00, 5.00),
    (103, N'Safety Gloves', 3, 6.00, 12.00),
    (104, N'Label Printer', 1, 120.00, 199.00),
    (105, N'Storage Bin', 4, 18.00, 35.00),
    (106, N'Barcode Labels', 2, 8.00, 16.00);

What do the product rows provide?

  • Each product references one of the four seeded suppliers.
  • The cost and price columns support later operational and revenue calculations.
  • Save 01_schema_and_seed.sql.
  • Select only the product insert statement.
  • Press Ctrl+Shift+E to insert the products.

The product table now contains six supplier-linked items.

Product rows were rejected?

  • Confirm that all four supplier rows were inserted first.
  • Check each product row for five values in the listed column order.

Get help with the insert: Help me diagnose the Products seed rows.

  • Add three warehouse records on a blank line by pasting this code:
INSERT INTO dbo.Warehouses (warehouse_id, warehouse_name)
VALUES
    (1, N'East Hub'),
    (2, N'Central Hub'),
    (3, N'West Hub');

What do the warehouse rows represent?

  • The three locations provide valid warehouse references for inventory snapshots and sales orders.
  • Save 01_schema_and_seed.sql.
  • Select only the warehouse insert statement.
  • Press Ctrl+Shift+E to insert the warehouse records.

The warehouse table now contains East Hub, Central Hub, and West Hub.

Warehouse rows were not inserted?

  • Confirm that the three warehouse identifiers are unique.
  • Check that each row contains one identifier and one name.

Ask for focused support: Help me diagnose the Warehouses insert statement.

  • Add the inventory history on a blank line by pasting this code:
INSERT INTO dbo.InventorySnapshots
    (snapshot_date, warehouse_id, product_id, quantity_on_hand, reorder_level)
VALUES
    ('2026-01-05', 1, 101, 5, 8),
    ('2026-01-12', 1, 101, 0, 8),
    ('2026-01-19', 1, 101, 0, 8),
    ('2026-01-26', 1, 101, 12, 8),
    ('2026-01-05', 1, 102, 30, 15),
    ('2026-01-12', 1, 102, 25, 15),
    ('2026-01-19', 1, 102, 20, 15),
    ('2026-01-26', 1, 102, 18, 15),
    ('2026-01-05', 2, 103, 8, 7),
    ('2026-01-12', 2, 103, 0, 7),
    ('2026-01-19', 2, 103, 6, 7),
    ('2026-01-26', 2, 103, 4, 7),
    ('2026-01-05', 1, 104, 2, 2),
    ('2026-01-12', 1, 104, 1, 2),
    ('2026-01-19', 1, 104, 0, 2),
    ('2026-01-26', 1, 104, 3, 2),
    ('2026-01-05', 3, 105, 20, 10),
    ('2026-01-12', 3, 105, 18, 10),
    ('2026-01-19', 3, 105, 16, 10),
    ('2026-01-26', 3, 105, 15, 10),
    ('2026-01-05', 2, 106, 0, 12),
    ('2026-01-12', 2, 106, 0, 12),
    ('2026-01-19', 2, 106, 0, 12),
    ('2026-01-26', 2, 106, 10, 12);

Why seed dated snapshots?

  • The 24 rows record four observation dates for each product.
  • Earlier zero quantities preserve historical stockout evidence.
  • The latest records provide the current inventory state used in the next step.
  • Save 01_schema_and_seed.sql.
  • Select only the inventory-snapshot insert statement.
  • Press Ctrl+Shift+E to insert the inventory history.

The database now holds 24 dated inventory observations.

Inventory rows were rejected?

  • Confirm that all referenced product and warehouse rows already exist.
  • Check that no date, warehouse, and product combination appears twice.

Use this prompt to inspect the data: Help me diagnose the InventorySnapshots seed rows.

  • Add eight purchase orders on a blank line by pasting this code:
INSERT INTO dbo.PurchaseOrders
    (purchase_order_id, supplier_id, order_date, promised_delivery_date, received_date)
VALUES
    (2001, 1, '2025-12-01', '2025-12-10', '2025-12-14'),
    (2002, 1, '2025-12-20', '2025-12-28', '2026-01-03'),
    (2003, 2, '2025-12-02', '2025-12-09', '2025-12-08'),
    (2004, 2, '2025-12-15', '2025-12-22', '2025-12-22'),
    (2005, 3, '2025-12-01', '2025-12-12', '2025-12-18'),
    (2006, 3, '2025-12-20', '2026-01-02', '2026-01-12'),
    (2007, 4, '2025-12-05', '2025-12-20', '2025-12-18'),
    (2008, 4, '2025-12-22', '2026-01-06', '2026-01-08');

What do these purchase orders capture?

  • Each order records its supplier, order date, promised date, and receipt date.
  • These dates support the later lead-time and delivery-delay calculations.
  • Save 01_schema_and_seed.sql.
  • Select only the purchase-order insert statement.
  • Press Ctrl+Shift+E to insert the purchase orders.

The purchase-order table now contains eight supplier deliveries.

Purchase-order rows were rejected?

  • Confirm that every supplier identifier exists in dbo.Suppliers.
  • Check each row against the promised-date and receipt-date constraint.

Ask for date-specific help: Help me diagnose the PurchaseOrders seed rows.

  • Add four sales orders on a blank line by pasting this code:
INSERT INTO dbo.SalesOrders (sales_order_id, order_date, warehouse_id)
VALUES
    (5001, '2026-01-12', 1),
    (5002, '2026-01-12', 2),
    (5003, '2026-01-19', 1),
    (5004, '2026-01-19', 2);

What do the sales orders establish?

  • The four order headers connect dated customer demand to valid warehouse locations.
  • Save 01_schema_and_seed.sql.
  • Select only the sales-order insert statement.
  • Press Ctrl+Shift+E to insert the sales orders.

The sales-order table now contains four order headers.

Sales orders were not inserted?

  • Confirm that warehouse identifiers 1 and 2 exist.
  • Check that each sales-order identifier is unique.

Get focused help here: Help me diagnose the SalesOrders seed rows.

  • Add eight sales-order lines on a blank line by pasting this code:
INSERT INTO dbo.SalesOrderLines
    (sales_order_id, product_id, quantity_ordered, quantity_fulfilled, unit_price, unfulfilled_reason)
VALUES
    (5001, 101, 15, 0, 79.00, N'Stockout'),
    (5001, 102, 40, 40, 5.00, NULL),
    (5002, 103, 20, 5, 12.00, N'Stockout'),
    (5002, 105, 12, 12, 35.00, NULL),
    (5003, 101, 10, 2, 79.00, N'Stockout'),
    (5003, 104, 3, 1, 199.00, N'Stockout'),
    (5004, 106, 180, 0, 16.00, N'Stockout'),
    (5004, 103, 15, 12, 12.00, N'Other');

How do these lines preserve lost demand?

  • Each line records ordered units beside fulfilled units.
  • The recorded unit price supports a transparent revenue estimate.
  • The unfulfilled reason separates stockout-attributed demand from other shortfalls.
  • Save 01_schema_and_seed.sql.
  • Select only the sales-order-line insert statement.
  • Press Ctrl+Shift+E to insert the sales-order lines.

The sales model now contains eight product-level demand records.

Sales-order lines were rejected?

  • Confirm that every referenced sales order and product already exists.
  • Check that each fulfilled quantity stays between zero and its ordered quantity.

Ask for constraint help: Help me diagnose the SalesOrderLines seed rows.

  • Add the final row-count query on a blank line by pasting this code:
SELECT N'Suppliers' AS table_name, COUNT(*) AS row_count
FROM dbo.Suppliers
UNION ALL
SELECT N'Products', COUNT(*)
FROM dbo.Products
UNION ALL
SELECT N'Warehouses', COUNT(*)
FROM dbo.Warehouses
UNION ALL
SELECT N'InventorySnapshots', COUNT(*)
FROM dbo.InventorySnapshots
UNION ALL
SELECT N'PurchaseOrders', COUNT(*)
FROM dbo.PurchaseOrders
UNION ALL
SELECT N'SalesOrders', COUNT(*)
FROM dbo.SalesOrders
UNION ALL
SELECT N'SalesOrderLines', COUNT(*)
FROM dbo.SalesOrderLines;

What does the row-count query prove?

  • Each COUNT(*) measures the records in one project table.
  • The UNION ALL operators place all seven counts in one result set.
  • The result exposes any missing seed records before later analysis begins.

Before you run the check, which table do you expect to have the largest row count?

  • Save 01_schema_and_seed.sql.
  • Select only the final row-count query.
  • Press Ctrl+Shift+E to verify the seeded database.

You'll see 4 suppliers, 6 products, 3 warehouses, 24 inventory snapshots, 8 purchase orders, 4 sales orders, and 8 sales-order lines in the Results pane.

Row counts do not match?

  • Find the table with the unexpected count in the result set.
  • Return to that table's insert statement.
  • Confirm that its earlier execution completed successfully before running anything again.

Share the mismatched table name without sharing private data: Help me diagnose a mismatched seed-data row count.

  • Refresh SupplyChainPortfolio in the MSSQL Connections view.
  • Expand SupplyChainPortfolio.
  • Expand Tables to confirm that all seven dbo tables are listed.

That's the database foundation complete. Every later stockout and supplier metric now traces back to these connected records.

✔️ Awesome, I've got everything!

Great. Save 01_schema_and_seed.sql with all seven tables, all seed records, and the row-count query.

ⓧ I'd like to double check the full code

USE SupplyChainPortfolio;

CREATE TABLE dbo.Suppliers
(
    supplier_id INT NOT NULL PRIMARY KEY,
    supplier_name NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Products
(
    product_id INT NOT NULL PRIMARY KEY,
    product_name NVARCHAR(100) NOT NULL,
    supplier_id INT NOT NULL,
    unit_cost DECIMAL(10, 2) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT FK_Products_Suppliers
        FOREIGN KEY (supplier_id) REFERENCES dbo.Suppliers(supplier_id)
);

CREATE TABLE dbo.Warehouses
(
    warehouse_id INT NOT NULL PRIMARY KEY,
    warehouse_name NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.InventorySnapshots
(
    snapshot_date DATE NOT NULL,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity_on_hand INT NOT NULL,
    reorder_level INT NOT NULL,
    CONSTRAINT PK_InventorySnapshots
        PRIMARY KEY (snapshot_date, warehouse_id, product_id),
    CONSTRAINT FK_InventorySnapshots_Warehouses
        FOREIGN KEY (warehouse_id) REFERENCES dbo.Warehouses(warehouse_id),
    CONSTRAINT FK_InventorySnapshots_Products
        FOREIGN KEY (product_id) REFERENCES dbo.Products(product_id),
    CONSTRAINT CK_InventorySnapshots_Quantity
        CHECK (quantity_on_hand >= 0 AND reorder_level >= 0)
);

CREATE TABLE dbo.PurchaseOrders
(
    purchase_order_id INT NOT NULL PRIMARY KEY,
    supplier_id INT NOT NULL,
    order_date DATE NOT NULL,
    promised_delivery_date DATE NOT NULL,
    received_date DATE NULL,
    CONSTRAINT FK_PurchaseOrders_Suppliers
        FOREIGN KEY (supplier_id) REFERENCES dbo.Suppliers(supplier_id),
    CONSTRAINT CK_PurchaseOrders_Dates
        CHECK
        (
            promised_delivery_date >= order_date
            AND (received_date IS NULL OR received_date >= order_date)
        )
);

CREATE TABLE dbo.SalesOrders
(
    sales_order_id INT NOT NULL PRIMARY KEY,
    order_date DATE NOT NULL,
    warehouse_id INT NOT NULL,
    CONSTRAINT FK_SalesOrders_Warehouses
        FOREIGN KEY (warehouse_id) REFERENCES dbo.Warehouses(warehouse_id)
);

CREATE TABLE dbo.SalesOrderLines
(
    sales_order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity_ordered INT NOT NULL,
    quantity_fulfilled INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    unfulfilled_reason NVARCHAR(20) NULL,
    CONSTRAINT PK_SalesOrderLines
        PRIMARY KEY (sales_order_id, product_id),
    CONSTRAINT FK_SalesOrderLines_SalesOrders
        FOREIGN KEY (sales_order_id) REFERENCES dbo.SalesOrders(sales_order_id),
    CONSTRAINT FK_SalesOrderLines_Products
        FOREIGN KEY (product_id) REFERENCES dbo.Products(product_id),
    CONSTRAINT CK_SalesOrderLines_Quantities
        CHECK
        (
            quantity_ordered > 0
            AND quantity_fulfilled BETWEEN 0 AND quantity_ordered
        ),
    CONSTRAINT CK_SalesOrderLines_Reason
        CHECK
        (
            unfulfilled_reason IN (N'Stockout', N'Other')
            OR unfulfilled_reason IS NULL
        )
);

INSERT INTO dbo.Suppliers (supplier_id, supplier_name)
VALUES
    (1, N'Apex Components'),
    (2, N'Northstar Goods'),
    (3, N'BlueRiver Supply'),
    (4, N'Summit Industrial');

INSERT INTO dbo.Products
    (product_id, product_name, supplier_id, unit_cost, unit_price)
VALUES
    (101, N'Wireless Scanner', 1, 45.00, 79.00),
    (102, N'Packing Tape', 2, 2.00, 5.00),
    (103, N'Safety Gloves', 3, 6.00, 12.00),
    (104, N'Label Printer', 1, 120.00, 199.00),
    (105, N'Storage Bin', 4, 18.00, 35.00),
    (106, N'Barcode Labels', 2, 8.00, 16.00);

INSERT INTO dbo.Warehouses (warehouse_id, warehouse_name)
VALUES
    (1, N'East Hub'),
    (2, N'Central Hub'),
    (3, N'West Hub');

INSERT INTO dbo.InventorySnapshots
    (snapshot_date, warehouse_id, product_id, quantity_on_hand, reorder_level)
VALUES
    ('2026-01-05', 1, 101, 5, 8),
    ('2026-01-12', 1, 101, 0, 8),
    ('2026-01-19', 1, 101, 0, 8),
    ('2026-01-26', 1, 101, 12, 8),
    ('2026-01-05', 1, 102, 30, 15),
    ('2026-01-12', 1, 102, 25, 15),
    ('2026-01-19', 1, 102, 20, 15),
    ('2026-01-26', 1, 102, 18, 15),
    ('2026-01-05', 2, 103, 8, 7),
    ('2026-01-12', 2, 103, 0, 7),
    ('2026-01-19', 2, 103, 6, 7),
    ('2026-01-26', 2, 103, 4, 7),
    ('2026-01-05', 1, 104, 2, 2),
    ('2026-01-12', 1, 104, 1, 2),
    ('2026-01-19', 1, 104, 0, 2),
    ('2026-01-26', 1, 104, 3, 2),
    ('2026-01-05', 3, 105, 20, 10),
    ('2026-01-12', 3, 105, 18, 10),
    ('2026-01-19', 3, 105, 16, 10),
    ('2026-01-26', 3, 105, 15, 10),
    ('2026-01-05', 2, 106, 0, 12),
    ('2026-01-12', 2, 106, 0, 12),
    ('2026-01-19', 2, 106, 0, 12),
    ('2026-01-26', 2, 106, 10, 12);

INSERT INTO dbo.PurchaseOrders
    (purchase_order_id, supplier_id, order_date, promised_delivery_date, received_date)
VALUES
    (2001, 1, '2025-12-01', '2025-12-10', '2025-12-14'),
    (2002, 1, '2025-12-20', '2025-12-28', '2026-01-03'),
    (2003, 2, '2025-12-02', '2025-12-09', '2025-12-08'),
    (2004, 2, '2025-12-15', '2025-12-22', '2025-12-22'),
    (2005, 3, '2025-12-01', '2025-12-12', '2025-12-18'),
    (2006, 3, '2025-12-20', '2026-01-02', '2026-01-12'),
    (2007, 4, '2025-12-05', '2025-12-20', '2025-12-18'),
    (2008, 4, '2025-12-22', '2026-01-06', '2026-01-08');

INSERT INTO dbo.SalesOrders (sales_order_id, order_date, warehouse_id)
VALUES
    (5001, '2026-01-12', 1),
    (5002, '2026-01-12', 2),
    (5003, '2026-01-19', 1),
    (5004, '2026-01-19', 2);

INSERT INTO dbo.SalesOrderLines
    (sales_order_id, product_id, quantity_ordered, quantity_fulfilled, unit_price, unfulfilled_reason)
VALUES
    (5001, 101, 15, 0, 79.00, N'Stockout'),
    (5001, 102, 40, 40, 5.00, NULL),
    (5002, 103, 20, 5, 12.00, N'Stockout'),
    (5002, 105, 12, 12, 35.00, NULL),
    (5003, 101, 10, 2, 79.00, N'Stockout'),
    (5003, 104, 3, 1, 199.00, N'Stockout'),
    (5004, 106, 180, 0, 16.00, N'Stockout'),
    (5004, 103, 15, 12, 12.00, N'Other');

SELECT N'Suppliers' AS table_name, COUNT(*) AS row_count
FROM dbo.Suppliers
UNION ALL
SELECT N'Products', COUNT(*)
FROM dbo.Products
UNION ALL
SELECT N'Warehouses', COUNT(*)
FROM dbo.Warehouses
UNION ALL
SELECT N'InventorySnapshots', COUNT(*)
FROM dbo.InventorySnapshots
UNION ALL
SELECT N'PurchaseOrders', COUNT(*)
FROM dbo.PurchaseOrders
UNION ALL
SELECT N'SalesOrders', COUNT(*)
FROM dbo.SalesOrders
UNION ALL
SELECT N'SalesOrderLines', COUNT(*)
FROM dbo.SalesOrderLines;

How to use this reference

Compare this full file with your saved 01_schema_and_seed.sql. It contains the seven table definitions, all seed statements, and the final row-count query.

Your connected supply-chain dataset is ready. Next, you'll test whether a current inventory snapshot can reveal the stockout risk hidden in its history.

Discover the Current-Inventory Blind Spot

Your seven-table SQL Server database now holds the supply chain history. Each result has a traceable source.

A decision based only on current inventory may miss earlier outages. In this step, you will test the latest snapshot before deciding whether it can reveal repeated stockout risk.

In this step, get ready to:
  • Create a query that returns the latest inventory snapshot.
  • Test the latest snapshot with a zero-stock filter.
  • Compare the two results to identify the current-inventory blind spot.
Query the latest inventory snapshot

A point-in-time query uses the latest recorded date to show current inventory. Joining the product and warehouse tables turns numeric identifiers into names you can interpret.

  • Create 02_current_inventory.sql inside Documents\supply-chain-stockout-analysis using the file list in Visual Studio Code.
  • Add the latest-snapshot query by pasting the code below:
USE SupplyChainPortfolio;

SELECT
    i.snapshot_date,
    p.product_name,
    w.warehouse_name,
    i.quantity_on_hand,
    i.reorder_level
FROM dbo.InventorySnapshots AS i
INNER JOIN dbo.Products AS p
    ON p.product_id = i.product_id
INNER JOIN dbo.Warehouses AS w
    ON w.warehouse_id = i.warehouse_id
WHERE i.snapshot_date =
(
    SELECT MAX(snapshot_date)
    FROM dbo.InventorySnapshots
)
ORDER BY p.product_name;

How the Latest-Snapshot Query Works

  • The aliases i, p, and w keep the table references concise.
  • The INNER JOIN clauses connect each snapshot to its product and warehouse.
  • The MAX(snapshot_date) subquery identifies the most recent recorded date.
  • The outer WHERE clause keeps only rows from that date.
  • Save 02_current_inventory.sql.

Before you run it, predict whether every product has stock in the latest snapshot.

  • Run the query by pressing Ctrl+Shift+E.

You will see six rows dated 2026-01-26. Every quantity_on_hand value is positive.

Missing the Six Current Rows?

  • Confirm SupplyChainPortfolio is the active database for the file.
  • Compare the table aliases with i, p, and w in the code above.
  • Use this query troubleshooting prompt to diagnose the result.
Test the zero-stock filter

A current stockout report adds a zero-quantity filter to the latest snapshot. Running that filter tests how much risk the point-in-time view can actually reveal.

  • Return to 02_current_inventory.sql from earlier.
  • Place your cursor after the first query.
  • Add the zero-stock query by pasting the code below:
SELECT
    p.product_name,
    w.warehouse_name,
    i.quantity_on_hand
FROM dbo.InventorySnapshots AS i
INNER JOIN dbo.Products AS p
    ON p.product_id = i.product_id
INNER JOIN dbo.Warehouses AS w
    ON w.warehouse_id = i.warehouse_id
WHERE i.snapshot_date =
(
    SELECT MAX(snapshot_date)
    FROM dbo.InventorySnapshots
)
AND i.quantity_on_hand = 0
ORDER BY p.product_name;

What Does the Filter Test?

  • The latest-date condition keeps the analysis focused on the current snapshot.
  • The i.quantity_on_hand = 0 condition keeps products with no current stock.
  • The second result set provides a direct test of whether current inventory exposes the seeded outages.
  • Save 02_current_inventory.sql.

Before you run the full file, predict whether the zero-stock filter will reveal the outages stored in the seeded history.

  • Run the full file by pressing Ctrl+Shift+E.

The first result set shows six rows dated 2026-01-26. Every product has a positive current quantity.

The second result set shows column headers with no data rows. That empty result is the intended shortfall.

The latest snapshot answers what is in stock now. Earlier outages remain hidden.

Seeing Unexpected Zero-Stock Rows?

  • Confirm the filter reads i.quantity_on_hand = 0.
  • Confirm the MAX(snapshot_date) subquery remains above the zero-stock filter.
  • Use this result-set troubleshooting prompt to compare your query.

✔️ Awesome, I've got everything!

Your two result sets confirm that the current-inventory file is complete.

ⓧ I'd like to double check the full code

USE SupplyChainPortfolio;

SELECT
    i.snapshot_date,
    p.product_name,
    w.warehouse_name,
    i.quantity_on_hand,
    i.reorder_level
FROM dbo.InventorySnapshots AS i
INNER JOIN dbo.Products AS p
    ON p.product_id = i.product_id
INNER JOIN dbo.Warehouses AS w
    ON w.warehouse_id = i.warehouse_id
WHERE i.snapshot_date =
(
    SELECT MAX(snapshot_date)
    FROM dbo.InventorySnapshots
)
ORDER BY p.product_name;

SELECT
    p.product_name,
    w.warehouse_name,
    i.quantity_on_hand
FROM dbo.InventorySnapshots AS i
INNER JOIN dbo.Products AS p
    ON p.product_id = i.product_id
INNER JOIN dbo.Warehouses AS w
    ON w.warehouse_id = i.warehouse_id
WHERE i.snapshot_date =
(
    SELECT MAX(snapshot_date)
    FROM dbo.InventorySnapshots
)
AND i.quantity_on_hand = 0
ORDER BY p.product_name;

You have exposed the gap between current stock and historical risk. Next, you will use the earlier snapshots to measure stockout impact.

Reveal Historical Stockout Impact

Your latest-snapshot query showed six products with stock on hand. The empty zero-stock result exposed the weakness of a current-inventory view.

Historical snapshots reveal which products repeatedly reached zero. Stockout-attributed unfulfilled units add a revenue-impact estimate to that history.

In this step, get ready to:
  • Count inventory observations for each product.
  • Measure zero-stock snapshots with conditional aggregation.
  • Rank product impact using stockout frequency and estimated lost revenue.
Create the historical metrics

A common table expression gives a calculation a reusable name inside one statement. This report uses one CTE for snapshot frequency and another for revenue impact.

  • Return to the project file list in Visual Studio Code.
  • Select the new-file icon beside the supply-chain-stockout-analysis folder.
  • Name the file 03_stockout_impact.sql.

You should see the blank 03_stockout_impact.sql file beside the three files from earlier.

  • Add the database selection and both metric CTEs by pasting this code into 03_stockout_impact.sql:
USE SupplyChainPortfolio;

WITH SnapshotCounts AS
(
    SELECT
        product_id,
        COUNT(*) AS observed_snapshot_count,
        SUM(CASE WHEN quantity_on_hand = 0 THEN 1 ELSE 0 END)
            AS stockout_snapshot_count
    FROM dbo.InventorySnapshots
    GROUP BY product_id
),
StockoutLostRevenue AS
(
    SELECT
        product_id,
        SUM
        (
            CASE
                WHEN unfulfilled_reason = N'Stockout'
                THEN (quantity_ordered - quantity_fulfilled) * unit_price
                ELSE 0.00
            END
        ) AS estimated_lost_revenue
    FROM dbo.SalesOrderLines
    GROUP BY product_id
)

What do these CTEs measure?

  • The SnapshotCounts CTE groups every inventory observation by product_id.
  • The conditional SUM adds one when quantity_on_hand equals 0.
  • The StockoutLostRevenue CTE totals unfulfilled units attributed to N'Stockout'.
  • Each unfulfilled unit is multiplied by its recorded unit_price to produce estimated_lost_revenue.
  • Save 03_stockout_impact.sql.
  • Confirm the file tab no longer shows an unsaved indicator.
Add the product-risk leaderboard

The final query joins both metrics to the product names. DENSE_RANK() assigns the same position to products with matching risk values without leaving gaps.

  • Place the cursor on a new line after the closing parenthesis of StockoutLostRevenue.
  • Complete the report by pasting this code:
SELECT
    DENSE_RANK() OVER
    (
        ORDER BY
            sc.stockout_snapshot_count DESC,
            COALESCE(lr.estimated_lost_revenue, 0.00) DESC
    ) AS product_risk_rank,
    p.product_name,
    sc.stockout_snapshot_count,
    sc.observed_snapshot_count,
    CAST
    (
        100.0 * sc.stockout_snapshot_count / sc.observed_snapshot_count
        AS DECIMAL(5, 2)
    ) AS stockout_snapshot_rate_pct,
    CAST(COALESCE(lr.estimated_lost_revenue, 0.00) AS DECIMAL(12, 2))
        AS estimated_lost_revenue
FROM SnapshotCounts AS sc
INNER JOIN dbo.Products AS p
    ON p.product_id = sc.product_id
LEFT JOIN StockoutLostRevenue AS lr
    ON lr.product_id = sc.product_id
ORDER BY product_risk_rank, p.product_name;

How does the leaderboard work?

  • The ranking prioritizes products with more zero-stock snapshots.
  • Estimated lost revenue breaks a tie between matching stockout counts.
  • The percentage divides each product's stockout snapshots by its observed snapshots.
  • The LEFT JOIN preserves products without stockout-attributed unfulfilled demand.
  • The COALESCE expressions represent missing revenue estimates as 0.00.
  • Save 03_stockout_impact.sql.
  • Confirm p.product_name is the final sort key.

✔️ Awesome, I've got everything!

Your saved 03_stockout_impact.sql file now contains both historical metrics and the ranked result.

ⓧ I'd like to double check the full code

USE SupplyChainPortfolio;

WITH SnapshotCounts AS
(
    SELECT
        product_id,
        COUNT(*) AS observed_snapshot_count,
        SUM(CASE WHEN quantity_on_hand = 0 THEN 1 ELSE 0 END)
            AS stockout_snapshot_count
    FROM dbo.InventorySnapshots
    GROUP BY product_id
),
StockoutLostRevenue AS
(
    SELECT
        product_id,
        SUM
        (
            CASE
                WHEN unfulfilled_reason = N'Stockout'
                THEN (quantity_ordered - quantity_fulfilled) * unit_price
                ELSE 0.00
            END
        ) AS estimated_lost_revenue
    FROM dbo.SalesOrderLines
    GROUP BY product_id
)
SELECT
    DENSE_RANK() OVER
    (
        ORDER BY
            sc.stockout_snapshot_count DESC,
            COALESCE(lr.estimated_lost_revenue, 0.00) DESC
    ) AS product_risk_rank,
    p.product_name,
    sc.stockout_snapshot_count,
    sc.observed_snapshot_count,
    CAST
    (
        100.0 * sc.stockout_snapshot_count / sc.observed_snapshot_count
        AS DECIMAL(5, 2)
    ) AS stockout_snapshot_rate_pct,
    CAST(COALESCE(lr.estimated_lost_revenue, 0.00) AS DECIMAL(12, 2))
        AS estimated_lost_revenue
FROM SnapshotCounts AS sc
INNER JOIN dbo.Products AS p
    ON p.product_id = sc.product_id
LEFT JOIN StockoutLostRevenue AS lr
    ON lr.product_id = sc.product_id
ORDER BY product_risk_rank, p.product_name;
Verify the risk leaderboard

Before you run the report, which product do you expect to rank first?

  • Execute the completed query by pressing Ctrl+Shift+E.

You'll see Barcode Labels at product_risk_rank 1 in the Results pane. Its row shows 3 stockout snapshots from 4 observations.

The stockout_snapshot_rate_pct value is 75.00. The estimated_lost_revenue value is 2880.00.

That closes the blind spot. Your report now reveals repeated historical outages that the healthy current snapshot concealed.

How should you interpret the revenue estimate?

The estimate multiplies stockout-attributed unfulfilled units by recorded unit price. It supports prioritization with a transparent calculation.

Keep the label estimated_lost_revenue when presenting the result. The value represents an estimate from the seeded operational records.

Report not matching the checkpoint?

Confirm SupplyChainPortfolio remains the active database. Check that both CTE names match the completed file.

If the editor highlights the final query, confirm the first CTE ends with a comma. Confirm the second CTE connects directly to the final SELECT.

Need another pair of eyes? Help me compare my stockout-impact query with the expected SQL structure.

Your product-risk leaderboard is ready. Next, you'll compare these product signals with supplier delivery performance.

Add Supplier Delivery Risk

Your product leaderboard now exposes repeated stockouts and estimated revenue impact. The remaining question is whether supplier delivery performance aligns with that risk.

Stockouts can have several causes. This step compares product risk with supplier lead time and delivery delay without assigning blame from association alone.

In this step, get ready to:
  • Calculate actual lead time for each supplier.
  • Rank suppliers by positive average delivery delay.
  • Compare supplier performance with product stockout impact.
Calculate supplier delivery metrics

The product metrics need a reusable shape before you can join them to supplier results. You will extend the previous report with a new common table expression for product impact.

  • Switch back to 03_stockout_impact.sql in Visual Studio Code.
  • Select all the SQL in the editor.
  • Copy the selected SQL.
  • Select the New File... button in the Explorer view.
  • Enter 04_supply_chain_risk_report.sql as the file name.
  • Paste the copied SQL into 04_supply_chain_risk_report.sql.

Build on the Working Report

Starting from 03_stockout_impact.sql preserves the product-risk logic you already verified. Each new supplier metric now has a known product result to join against.

  • Replace every occurrence of SnapshotCounts with ProductSnapshotMetrics.
  • Replace every occurrence of sc. with pm..
  • Replace AS sc with AS pm.
  • Save 04_supply_chain_risk_report.sql with Ctrl+S.
  • Execute the query with Ctrl+Shift+E.

You should still see the six-product leaderboard with Barcode Labels first. The rename has preserved the working result.

  • Add a comma after the closing parenthesis of StockoutLostRevenue.
  • Insert the following ProductImpact definition before the existing final SELECT statement:
ProductImpact AS
(
    SELECT
        p.product_id,
        p.product_name,
        p.supplier_id,
        pm.stockout_snapshot_count,
        pm.observed_snapshot_count,
        CAST
        (
            100.0 * pm.stockout_snapshot_count / pm.observed_snapshot_count
            AS DECIMAL(5, 2)
        ) AS stockout_snapshot_rate_pct,
        CAST(COALESCE(lr.estimated_lost_revenue, 0.00) AS DECIMAL(12, 2))
            AS estimated_lost_revenue
    FROM dbo.Products AS p
    INNER JOIN ProductSnapshotMetrics AS pm
        ON pm.product_id = p.product_id
    LEFT JOIN StockoutLostRevenue AS lr
        ON lr.product_id = p.product_id
)

What Does ProductImpact Do?

  • The ProductImpact result keeps each product's supplier identifier beside its stockout metrics.
  • The supplier identifier creates the bridge between product performance and supplier performance.
  • The existing rate calculation and estimated revenue calculation remain unchanged.
  • Save 04_supply_chain_risk_report.sql with Ctrl+S.
  • Execute the query with Ctrl+Shift+E.

You should see the same six product rows again. This confirms that the expanded CTE chain remains valid.

Seeing a CTE Syntax Error?

Check that the closing parenthesis before ProductImpact ends with a comma. Confirm that the closing parenthesis after ProductImpact has no comma yet.

Ask for help with the CTE boundary if the query still fails: Help me find the misplaced comma in my SQL CTE chain.

Lead Time Versus Delivery Delay

Actual lead time measures the span from order_date to received_date. It captures the full time taken to receive an order.

Delivery delay measures the span after promised_delivery_date when a receipt is late. Early receipts and on-time receipts contribute zero delay.

  • Add a comma after the closing parenthesis of ProductImpact.
  • Insert the initial SupplierDeliveryPerformance definition before the existing final SELECT statement:
SupplierDeliveryPerformance AS
(
    SELECT
        supplier_id,
        COUNT(*) AS purchase_order_count,
        CAST
        (
            AVG
            (
                CAST
                (
                    DATEDIFF(day, order_date, received_date)
                    AS DECIMAL(10, 2)
                )
            )
            AS DECIMAL(10, 2)
        ) AS average_actual_lead_time_days,
        SUM
        (
            CASE
                WHEN received_date > promised_delivery_date THEN 1
                ELSE 0
            END
        ) AS delayed_order_count
    FROM dbo.PurchaseOrders
    WHERE received_date IS NOT NULL
    GROUP BY supplier_id
)

What Do These Supplier Metrics Measure?

  • The purchase-order count shows how many received orders support each supplier's metrics.
  • The DATEDIFF(day, order_date, received_date) calculation measures actual lead time for each received order.
  • The conditional SUM counts orders received after their promised date.
  • Save 04_supply_chain_risk_report.sql with Ctrl+S.
  • Execute the query with Ctrl+Shift+E.

You should see the product leaderboard complete without a syntax error. SQL Server has accepted the supplier aggregation even though the final result does not display it yet.

  • Find the SUM that calculates delayed_order_count inside SupplierDeliveryPerformance.
  • Insert the following average-delay calculation directly above that SUM:
        CAST
        (
            AVG
            (
                CAST
                (
                    CASE
                        WHEN received_date > promised_delivery_date
                        THEN DATEDIFF(day, promised_delivery_date, received_date)
                        ELSE 0
                    END
                    AS DECIMAL(10, 2)
                )
            )
            AS DECIMAL(10, 2)
        ) AS average_delivery_delay_days,

How Does Positive Delay Work?

  • The CASE expression calculates days of delay only when receipt occurs after the promised date.
  • The ELSE 0 branch records early receipts and on-time receipts as zero delay.
  • The outer AVG produces one comparable delay value for each supplier.
  • Save 04_supply_chain_risk_report.sql with Ctrl+S.
  • Execute the query with Ctrl+Shift+E.

You should see the six product rows again. This successful run confirms that the positive-delay calculation is valid.

Does the Delay Calculation Fail?

Confirm that the new CAST expression ends with a comma before SUM. Check that every opening parenthesis has a matching closing parenthesis.

Ask for a focused review if the nesting is difficult to trace: Help me check my average delivery delay expression.

Rank suppliers by average delay

The supplier metrics are calculated now. The final query brings those metrics beside each product before ranking suppliers by average delivery delay.

  • Select the existing final SELECT statement after the CTE chain.
  • Replace that selected statement with the following joined report:
SELECT
    DENSE_RANK() OVER
    (
        ORDER BY
            pi.stockout_snapshot_count DESC,
            pi.estimated_lost_revenue DESC
    ) AS product_risk_rank,
    pi.product_name,
    pi.stockout_snapshot_count,
    pi.stockout_snapshot_rate_pct,
    pi.estimated_lost_revenue,
    s.supplier_name,
    sp.purchase_order_count,
    sp.average_actual_lead_time_days,
    sp.average_delivery_delay_days,
    sp.delayed_order_count
FROM ProductImpact AS pi
INNER JOIN dbo.Suppliers AS s
    ON s.supplier_id = pi.supplier_id
INNER JOIN SupplierDeliveryPerformance AS sp
    ON sp.supplier_id = pi.supplier_id
ORDER BY product_risk_rank, pi.product_name;

What Does the Join Add?

  • The ProductImpact rows retain each product's stockout frequency and estimated revenue impact.
  • The join to dbo.Suppliers adds the supplier name for each product.
  • The join to SupplierDeliveryPerformance adds actual lead time and positive delivery delay.

Before you run this version, which supplier do you expect to have the longest average actual lead time?

  • Save 04_supply_chain_risk_report.sql with Ctrl+S.
  • Execute the joined report with Ctrl+Shift+E.

You should see BlueRiver Supply with an average actual lead time of 20.00 days. Its average delivery delay should be 8.00 days.

  • Add a comma after sp.delayed_order_count in the final column list.
  • Insert the supplier ranking expression directly below that column:
    DENSE_RANK() OVER
    (
        ORDER BY sp.average_delivery_delay_days DESC
    ) AS supplier_delay_rank

How Does the Supplier Rank Work?

The ranking orders suppliers from the highest average delivery delay to the lowest. DENSE_RANK() gives equal delays the same rank without leaving gaps.

Before you rerun the report, which supplier should receive supplier-delay rank 1 based on the values you just observed?

  • Save 04_supply_chain_risk_report.sql with Ctrl+S.
  • Execute the ranked report with Ctrl+Shift+E.

You should see BlueRiver Supply at supplier-delay rank 1. The row also shows its 20.00-day average actual lead time and 8.00-day average delivery delay.

Missing the Supplier Rank?

Check that sp.delayed_order_count ends with a comma. Confirm that the supplier ranking expression appears before FROM ProductImpact AS pi.

Ask for help if the column list still fails: Help me fix the supplier rank in my final SELECT.

✔️ Awesome, I've got everything!

Your saved report now combines product stockout impact with supplier delivery performance. Keep 04_supply_chain_risk_report.sql open for the final comparison.

ⓧ I'd like to double check the full code

USE SupplyChainPortfolio;

WITH ProductSnapshotMetrics AS
(
    SELECT
        product_id,
        COUNT(*) AS observed_snapshot_count,
        SUM(CASE WHEN quantity_on_hand = 0 THEN 1 ELSE 0 END)
            AS stockout_snapshot_count
    FROM dbo.InventorySnapshots
    GROUP BY product_id
),
StockoutLostRevenue AS
(
    SELECT
        product_id,
        SUM
        (
            CASE
                WHEN unfulfilled_reason = N'Stockout'
                THEN (quantity_ordered - quantity_fulfilled) * unit_price
                ELSE 0.00
            END
        ) AS estimated_lost_revenue
    FROM dbo.SalesOrderLines
    GROUP BY product_id
),
ProductImpact AS
(
    SELECT
        p.product_id,
        p.product_name,
        p.supplier_id,
        pm.stockout_snapshot_count,
        pm.observed_snapshot_count,
        CAST
        (
            100.0 * pm.stockout_snapshot_count / pm.observed_snapshot_count
            AS DECIMAL(5, 2)
        ) AS stockout_snapshot_rate_pct,
        CAST(COALESCE(lr.estimated_lost_revenue, 0.00) AS DECIMAL(12, 2))
            AS estimated_lost_revenue
    FROM dbo.Products AS p
    INNER JOIN ProductSnapshotMetrics AS pm
        ON pm.product_id = p.product_id
    LEFT JOIN StockoutLostRevenue AS lr
        ON lr.product_id = p.product_id
),
SupplierDeliveryPerformance AS
(
    SELECT
        supplier_id,
        COUNT(*) AS purchase_order_count,
        CAST
        (
            AVG
            (
                CAST
                (
                    DATEDIFF(day, order_date, received_date)
                    AS DECIMAL(10, 2)
                )
            )
            AS DECIMAL(10, 2)
        ) AS average_actual_lead_time_days,
        CAST
        (
            AVG
            (
                CAST
                (
                    CASE
                        WHEN received_date > promised_delivery_date
                        THEN DATEDIFF(day, promised_delivery_date, received_date)
                        ELSE 0
                    END
                    AS DECIMAL(10, 2)
                )
            )
            AS DECIMAL(10, 2)
        ) AS average_delivery_delay_days,
        SUM
        (
            CASE
                WHEN received_date > promised_delivery_date THEN 1
                ELSE 0
            END
        ) AS delayed_order_count
    FROM dbo.PurchaseOrders
    WHERE received_date IS NOT NULL
    GROUP BY supplier_id
)
SELECT
    DENSE_RANK() OVER
    (
        ORDER BY
            pi.stockout_snapshot_count DESC,
            pi.estimated_lost_revenue DESC
    ) AS product_risk_rank,
    pi.product_name,
    pi.stockout_snapshot_count,
    pi.stockout_snapshot_rate_pct,
    pi.estimated_lost_revenue,
    s.supplier_name,
    sp.purchase_order_count,
    sp.average_actual_lead_time_days,
    sp.average_delivery_delay_days,
    sp.delayed_order_count,
    DENSE_RANK() OVER
    (
        ORDER BY sp.average_delivery_delay_days DESC
    ) AS supplier_delay_rank
FROM ProductImpact AS pi
INNER JOIN dbo.Suppliers AS s
    ON s.supplier_id = pi.supplier_id
INNER JOIN SupplierDeliveryPerformance AS sp
    ON sp.supplier_id = pi.supplier_id
ORDER BY product_risk_rank, pi.product_name;

Compare this reference with your saved file from top to bottom. The CTE order and final joins should match exactly.

Compare product and supplier risk

The combined report lets you compare two rankings in the same row. The joined rows show association only because product stockouts can also reflect demand changes or internal inventory decisions.

  • Locate the row for Barcode Labels in the Results pane.
  • Confirm that Barcode Labels has product-risk rank 1.
  • Confirm that Barcode Labels belongs to Northstar Goods.
  • Locate the row for Safety Gloves in the Results pane.
  • Confirm that Safety Gloves belongs to BlueRiver Supply.
  • Compare the product-risk rank with the supplier-delay rank on both rows.

What Does the Comparison Prove?

Barcode Labels remains the highest-ranked product risk under Northstar Goods. BlueRiver Supply holds the highest supplier-delay rank through a different product.

That separation keeps the analysis defensible. Supplier delay is one risk signal within the report.

Before you run the final check, do you expect the highest product-risk row and the highest supplier-delay row to point to the same supplier?

  • Execute the full 04_supply_chain_risk_report.sql file with Ctrl+Shift+E.

You should see Barcode Labels at product-risk rank 1 under Northstar Goods. You should also see BlueRiver Supply at supplier-delay rank 1 with 20.00 average actual lead-time days and 8.00 average delivery-delay days.

That is the final risk report working. You can now compare downstream stockout impact with upstream delivery performance without presenting the relationship as proof of causation.

Secret mission

Build a Restocking Watchlist

Turn your retrospective SQL analysis into a current restocking watchlist. Flag products at or below their reorder levels and calculate a transparent practice quantity.

Clean Up Your Resources

Clean Up Your Resources

This project leaves two local resources to manage: the SQL Server database plus its six SQL files. Keeping either resource creates no ongoing charge because everything costs $0.

Resources you used:

  • Your local SupplyChainPortfolio database on .\SQLEXPRESS.
  • Your Documents\supply-chain-stockout-analysis folder containing six .sql files.

Keep everything running

Leave everything in place if you plan to revisit the analysis or build the follow-on Power BI dashboard. No action is needed.

  • Retain SupplyChainPortfolio on .\SQLEXPRESS for future queries.
  • Keep Documents\supply-chain-stockout-analysis so the six reports remain available.
  • Use your existing Windows Authentication connection when you return.

Pause - I'll come back to this later

Pause the local database engine to free memory while preserving your work. The SQL files remain available while the database service is stopped.

  • Press the Windows key to open search.
  • Enter SQL Server Configuration Manager in the search field.
  • Open the matching result.
  • Stop SQL Server (SQLEXPRESS) from its service controls.
  • Start SQL Server (SQLEXPRESS) from the same controls when you return.

Your database remains intact while the service is stopped. You can query the same data after restarting the service.

Delete - I don't want to use this again

Deletion is permanent. Your six SQL files are easy to preserve with a quick backup.

  • Copy the Documents\supply-chain-stockout-analysis folder to a backup location if you want to reuse the queries.
  • Return to the .\SQLEXPRESS connection in Visual Studio Code.
  • Create a new query against master from the MSSQL Connections view.
  • Delete the local database by running this command:
DROP DATABASE SupplyChainPortfolio;

What does this command do?

The DROP DATABASE command permanently removes SupplyChainPortfolio with its seven tables plus all seeded data.

  • Confirm SupplyChainPortfolio no longer appears under .\SQLEXPRESS in the MSSQL Connections view.

Database Still in Use?

  • Close every query tab still connected to SupplyChainPortfolio.
  • Reconnect the delete query to master.
  • Run the database deletion command again.

Help me delete a SQL Server database that is still in use.

The database is gone from SQL Server once the check passes. The project folder remains on disk until you remove it.

  • Press the Windows key to open search.
  • Enter File Explorer in the search field.
  • Open the matching result.
  • Navigate to Documents.
  • Select supply-chain-stockout-analysis.
  • Delete the selected folder.
  • Confirm supply-chain-stockout-analysis no longer appears in Documents.

That clears both project resources from your computer. The installed tools can remain at no cost.

  • Remove the MSSQL extension from the Visual Studio Code Extensions view only if you do not need it for other projects.
  • Remove Visual Studio Code through the normal Windows uninstall controls only if you no longer need the editor.
  • Remove SQL Server 2025 Express through the normal Windows uninstall controls only if you no longer need a local database engine.

Nice Work!

Nice Work!

Your supply-chain analysis is complete! You built a local SQL analysis that reveals hidden stockout risk while comparing it with supplier delivery performance.

What you learned:

  • Created a relational data model across seven connected tables. Seeded it with traceable supply-chain records.
  • Used historical inventory snapshots to prove that healthy current stock can hide repeated outages.
  • Produced a stockout-impact leaderboard from stockout-snapshot frequency plus estimated lost revenue. Added supplier-delivery rankings from actual lead time plus positive delivery delay. Kept association separate from causation.
  • Secret Mission: Built a current restocking watchlist with a transparent project-defined heuristic. It suggests 10 units for Safety Gloves. It suggests 14 units for Barcode Labels.

Ready to quiz yourself?