Calculate reorder levels and safety stock from issue history in SQL

The mistake that makes slow items look fast

Stores and warehouse systems record transactions, not days. An item issued on ten days out of thirty has ten rows. A GROUP BY sku with AVG(qty) on those rows gives the average quantity per issue, not per day. For something like a toner cartridge used once every three days, that inflates demand threefold, the reorder level follows, and the storeroom fills with stock nobody needs.

The fix is a calendar: one row per SKU per day, with zero where nothing moved. SQLite can generate it with a recursive common table expression, so you do not need a separate date table.

Reorder points in SQLite

Everything below is synthetic: three stationery and consumables items issued from one store over thirty days of August 2026.

-- Synthetic data: 3 SKUs, 30 days of issues from a Noida warehouse, plus stock on hand.
CREATE TABLE sku (sku TEXT PRIMARY KEY, name TEXT, lead_days_avg REAL, lead_days_max REAL, on_hand INTEGER, on_order INTEGER);
INSERT INTO sku VALUES
 ('A4-RIM',  'A4 paper ream',        4, 7, 120, 0),
 ('TNR-88',  'Toner cartridge 88A',  10, 15, 9, 6),
 ('GLV-NTR', 'Nitrile gloves box',   6, 9, 40, 0);

CREATE TABLE issues (issue_date TEXT, sku TEXT, qty INTEGER);
WITH RECURSIVE d(n) AS (SELECT 0 UNION ALL SELECT n + 1 FROM d WHERE n < 29)
INSERT INTO issues
SELECT date('2026-08-01', '+' || n || ' days'), 'A4-RIM', 8 + (n * 7) % 9 FROM d
UNION ALL
SELECT date('2026-08-01', '+' || n || ' days'), 'TNR-88', 1 FROM d WHERE n % 3 = 0   -- only every third day
UNION ALL
SELECT date('2026-08-01', '+' || n || ' days'), 'GLV-NTR', 3 + (n * 5) % 4 FROM d WHERE n % 7 <> 6;  -- none on Sundays

-- Reorder point = expected demand over the lead time + safety stock.
-- Safety stock here uses the max-minus-average rule, which needs no statistics functions.
WITH RECURSIVE cal(day) AS (
    SELECT date('2026-08-01') UNION ALL SELECT date(day, '+1 day') FROM cal WHERE day < '2026-08-30'
),
daily AS (                      -- every SKU on every day, zero where nothing was issued
    SELECT s.sku, c.day, COALESCE(SUM(i.qty), 0) AS qty
    FROM sku s CROSS JOIN cal c
    LEFT JOIN issues i ON i.sku = s.sku AND i.issue_date = c.day
    GROUP BY s.sku, c.day
),
stats AS (
    SELECT sku,
           AVG(qty) AS avg_daily,
           MAX(qty) AS max_daily,
           (SELECT AVG(i.qty) FROM issues i WHERE i.sku = daily.sku) AS avg_on_issue_days
    FROM daily GROUP BY sku
),
reorder_plan AS (
    SELECT s.sku, s.name, st.avg_daily, st.avg_on_issue_days,
           st.max_daily * s.lead_days_max - st.avg_daily * s.lead_days_avg AS safety_stock,
           s.on_hand + s.on_order AS available,          -- count stock already ordered
           s.lead_days_avg
    FROM sku s JOIN stats st USING (sku)
)
SELECT sku, name,
       ROUND(avg_daily, 2)                                  AS avg_daily,
       ROUND(avg_on_issue_days, 2)                          AS wrong_avg_skip_zero_days,
       ROUND(safety_stock, 0)                               AS safety_stock,
       ROUND(avg_daily * lead_days_avg + safety_stock, 0)   AS reorder_point,
       available,
       CASE WHEN available <= avg_daily * lead_days_avg + safety_stock
            THEN 'REORDER NOW' ELSE 'ok' END                AS action
FROM reorder_plan
ORDER BY action DESC, sku;

The two average columns show the effect directly. Paper is issued every day, so both averages are 12. Gloves are issued on six days a week; the transaction average of 4.42 overstates the true 3.83. Toner is issued on every third day; the transaction average of 1.0 is three times the real 0.33 per day.

Two items need action. Gloves have 40 boxes on hand against a reorder point of 54. Toner has 9 cartridges on hand and 6 on order, 15 in all, which equals its reorder point of 15, so the rule says order now. Without counting the 6 already ordered, the toner line would have triggered a second purchase order for stock that is on its way.

Reading the safety stock formula

The query uses the maximum-minus-average rule:

It needs only AVG and MAX, runs in any SQL dialect or spreadsheet, and anyone in purchasing can check it by hand. Its weakness is that one unusual day in the history, such as a bulk issue for an event, sets max_daily for the whole period. Look at the maximum before trusting it, and exclude one-off bulk issues that will not recur.

The statistical alternative

When demand is fairly steady and you have a few months of daily history, the textbook formula is safety stock = z x σ x √L, where σ is the standard deviation of daily demand, L is lead time in days and z sets the service level: about 1.65 corresponds to covering demand in roughly 95% of lead-time periods under a normal-distribution assumption. Many SQLite builds, including the one shipped with recent Python versions, provide SQRT, but it is a compile-time option, so the statistical version is easier to finish in pandas:

import pandas as pd
# Synthetic daily demand for one SKU, zero days included.
daily = pd.Series([4, 3, 5, 0, 4, 6, 3, 4, 5, 3, 0, 4, 5, 4])
lead_days, z = 6, 1.65
safety = z * daily.std(ddof=1) * lead_days ** 0.5
reorder_point = daily.mean() * lead_days + safety
print(round(daily.mean(), 2), round(daily.std(ddof=1), 2), round(safety, 1), round(reorder_point, 1))

It does not suit lumpy items like the toner, where most days are zero; for those, a simple rule based on lead time and a minimum pack quantity is more honest than a formula that assumes a bell curve.

Inputs worth checking before the numbers go to purchasing

Lead time from real receipts. Take lead times from the gap between purchase order date and goods receipt date in your own records, not from the supplier's quotation. Keep both average and maximum.

Seasonality. Thirty days is enough for an example and too short for items that move with the festival season or the financial year end. For those, use the same months of the previous year, or compute levels separately for peak and normal periods. Compare festive sales when Diwali moves between October and November shows how to line up those periods.

Pack sizes and minimum order quantities. A reorder point of 54 boxes does not help if the supplier sells cartons of 50. Round the order quantity, not the reorder point.

Stock that is not usable. Damaged, expired or reserved stock should be excluded from on_hand before the comparison.

Running it regularly

Save the query, point it at the live issues and stock tables, and run it daily or weekly. Keep the output with its run date so that purchasing decisions can be traced back to the numbers that triggered them. Recompute the levels themselves monthly rather than daily, so they do not jump around with every busy day.

Inventory logic like this, from generating a calendar in SQL to deciding which formula suits which item, is part of the SQL and business-analysis practice in our Data Analyst programme.