Power BI - Essential SQL Queries for Supply Chain & Procurement

 

Essential SQL Queries for Supply Chain & Procurement

 Inventory Management

• Current stock levels by product:

SELECT ProductID, ProductName, SUM(Quantity) AS CurrentStock
FROM Inventory
GROUP BY ProductID, ProductName;

• Low stock alert (threshold = 100 units):

SELECT ProductID, ProductName, Quantity
FROM Inventory
WHERE Quantity < 100;

• Inventory turnover ratio:

SELECT ProductID, SUM(SalesQuantity) / AVG(Quantity) AS InventoryTurnover
FROM Inventory i
JOIN Sales s ON i.ProductID = s.ProductID
GROUP BY ProductID;

---

Procurement & Suppliers

• Total spend by supplier:

SELECT SupplierID, SUM(Amount) AS TotalSpend
FROM PurchaseOrders
GROUP BY SupplierID;

• Average lead time by supplier:

SELECT SupplierID, AVG(DATEDIFF(DAY, OrderDate, DeliveryDate)) AS AvgLeadTime
FROM PurchaseOrders
GROUP BY SupplierID;

• Supplier performance (on-time delivery rate):

SELECT SupplierID,
       SUM(CASE WHEN DeliveryStatus = 'On-Time' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS OnTimeRate
FROM Deliveries
GROUP BY SupplierID;

---

 Orders & Fulfillment

• Pending purchase orders:

SELECT OrderID, SupplierID, Status
FROM PurchaseOrders
WHERE Status = 'Pending';

• Order fulfillment rate:

SELECT SUM(CASE WHEN Status = 'Fulfilled' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS FulfillmentRate
FROM Orders;

• Backordered items:

SELECT ProductID, COUNT(*) AS BackorderCount
FROM Orders
WHERE Status = 'Backordered'
GROUP BY ProductID;

---

Cost & Efficiency

• Procurement cost by category:

SELECT Category, SUM(Cost) AS TotalCost
FROM Procurement
GROUP BY Category;

• Cost per unit purchased:

SELECT ProductID, SUM(Cost) / SUM(Quantity) AS CostPerUnit
FROM Procurement
GROUP BY ProductID;

• Supplier cost comparison:

SELECT SupplierID, AVG(UnitPrice) AS AvgUnitPrice
FROM PurchaseOrders
GROUP BY SupplierID;

---

Key Notes

• Indexes: Use on SupplierID, ProductID, OrderDate for performance.
• Dashboards: These queries feed KPIs like lead time, on-time delivery, procurement spend, and inventory turnover.
• Best practice: Always validate supplier data and ensure timestamps are consistent for accurate lead time calculations.

---