Power BI - Essential SQL Queries for Operations

 

Essential SQL Queries for Operations

Inventory Management

• Current stock levels by product:

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

• Low stock alert (threshold = 50 units):

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

• Average inventory turnover:

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

---

Logistics & Supply Chain

• 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;

• Average lead time by supplier:

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

• Pending shipments:

SELECT ShipmentID, OrderID, Status
FROM Shipments
WHERE Status = 'Pending';

---

Production & Operations

• Production output by plant:

SELECT PlantID, SUM(OutputUnits) AS TotalOutput
FROM Production
GROUP BY PlantID;

• Machine utilization rate:

SELECT MachineID,
       SUM(RunTime) * 100.0 / SUM(AvailableTime) AS UtilizationRate
FROM MachineLogs
GROUP BY MachineID;

• Defect rate in production:

SELECT ProductID,
       SUM(CASE WHEN DefectFlag = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DefectRate
FROM ProductionQuality
GROUP BY ProductID;

---

Cost & Efficiency

• Operational cost by department:

SELECT Department, SUM(Cost) AS TotalCost
FROM OperationsCosts
GROUP BY Department;

• Cost per unit produced:

SELECT ProductID, SUM(Cost) / SUM(OutputUnits) AS CostPerUnit
FROM ProductionCosts
GROUP BY ProductID;

• Supplier cost comparison:

SELECT SupplierID, AVG(CostPerUnit) AS AvgCost
FROM SupplierCosts
GROUP BY SupplierID;

---

Key Considerations

• Data integrity: Ensure timestamps (OrderDate, DeliveryDate) are consistent.
• Performance: For large datasets, use indexes on ProductID, SupplierID, and OrderID.
• Use cases: These queries feed into operations dashboards (Power BI,) for KPIs like lead time, defect rate, and utilization.

---