# SQL Monthly Demand History Query Generator for Forecasting

> Source: https://netray.co/tools/sql-monthly-demand-history-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate SQL that turns order or shipment lines into a clean monthly demand history per item, with gap months filled with zero.

## Category

developer

## Audience

- Demand planner
- ERP report developer
- Data analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Demand line table

**Key:** table

**Type:** text

**Default:** sales_order_lines

**Help:** Generic name. Replace with your ERP table or reporting view.

#### Item column

**Key:** item_col

**Type:** text

**Default:** item_code

#### Demand date column

**Key:** date_col

**Type:** text

**Default:** ship_date

**Help:** Use requested date for demand, shipped date for sales history.

#### Quantity column

**Key:** qty_col

**Type:** text

**Default:** qty_shipped

#### History start (first of month)

**Key:** start

**Type:** date

**Default:** 2024-01-01

#### History end (exclusive)

**Key:** end

**Type:** date

**Default:** 2026-10-01

#### Item filter (LIKE pattern)

**Key:** item_filter

**Type:** text

**Default:** BRK-%

**Blocks:**

#### PostgreSQL: monthly history with zero-filled gaps

**Body:** WITH months AS (
  SELECT generate_series(DATE '{{start}}', DATE '{{end}}' - INTERVAL '1 month', INTERVAL '1 month')::date AS month_start
),
items AS (
  SELECT DISTINCT {{item_col}} AS item
  FROM {{table}}
  WHERE {{item_col}} LIKE '{{item_filter}}'
),
monthly AS (
  SELECT {{item_col}} AS item,
         DATE_TRUNC('month', {{date_col}})::date AS month_start,
         SUM({{qty_col}}) AS qty
  FROM {{table}}
  WHERE {{date_col}} >= DATE '{{start}}'
    AND {{date_col}} < DATE '{{end}}'
    AND {{item_col}} LIKE '{{item_filter}}'
  GROUP BY {{item_col}}, DATE_TRUNC('month', {{date_col}})
)
SELECT i.item, m.month_start, COALESCE(d.qty, 0) AS qty
FROM items i
CROSS JOIN months m
LEFT JOIN monthly d ON d.item = i.item AND d.month_start = m.month_start
ORDER BY i.item, m.month_start;

#### SQL Server: monthly totals

**Body:** SELECT {{item_col}} AS item,
       DATEFROMPARTS(YEAR({{date_col}}), MONTH({{date_col}}), 1) AS month_start,
       SUM({{qty_col}}) AS qty
FROM {{table}}
WHERE {{date_col}} >= '{{start}}'
  AND {{date_col}} < '{{end}}'
  AND {{item_col}} LIKE '{{item_filter}}'
GROUP BY {{item_col}}, DATEFROMPARTS(YEAR({{date_col}}), MONTH({{date_col}}), 1)
ORDER BY {{item_col}}, month_start;

#### ADI inputs per item (PostgreSQL, run on the monthly result)

**Body:** -- Wrap the first query as monthly_full(item, month_start, qty)
SELECT item,
       COUNT(*) AS total_periods,
       COUNT(*) FILTER (WHERE qty > 0) AS nonzero_periods,
       AVG(qty) FILTER (WHERE qty > 0) AS mean_size,
       STDDEV_SAMP(qty) FILTER (WHERE qty > 0) AS sd_size
FROM monthly_full
GROUP BY item;

**Notes:** Table and column names are generic placeholders, not any specific ERP schema. Map them to your own table or view and remove cancelled, returned and intercompany lines before using the result as demand. Verify the date functions against your database version.

## Content

**Intro:** Every statistical forecast starts with a clean time series, and most ERPs store transactions, not time series. This generator produces SQL that totals demand by item and month, fills months with no sales with zero, and returns the counts used for demand classification.

**Sections:**

#### How to use the generator

**Body:** Fill in the table and column names from your ERP or reporting view, set the history window and an item filter, then copy the PostgreSQL or SQL Server block that matches your database. Run it, export the result to CSV and load it into Excel, Python or your planning tool. The zero-filled version matters, because missing months silently shorten the series and inflate averages.

#### Which date and quantity to use

**Body:** Shipped quantity by ship date shows what customers actually received, but understates demand when you were out of stock. Order quantity by requested date is closer to true demand. Many planners use order lines by requested date for the statistical baseline and keep shipments for service level reporting. Choose one, document it and keep it constant.

**Bullets:**

- Remove returns and cancelled lines
- Exclude intercompany transfers
- Decide how to treat one-off project orders
- Keep the unit of measure consistent

#### Reading the output

**Body:** You get one row per item per month, including zeros. A part with 36 rows and only 9 non-zero quantities is intermittent, which the third query quantifies with period counts, mean size and standard deviation. Feed those into the ADI and CV2 calculator to pick between smoothing and Croston style methods.

#### Common mistakes

**Body:** Grouping by a timestamp column without truncating to the month gives thousands of rows. Using an inner join instead of the cross join drops zero months. Mixing units of measure across lines adds boxes to pieces. Always check that the total over the query equals a trusted sales report for one item before you load everything.

**Cta:** If you would rather ask for this history in plain language and see the query it runs against your ERP, Netray can do that on-prem with read-only access.

**Faq:**

#### Why fill missing months with zero?

**Answer:** A month with no sales is a valid observation of zero demand. If you omit it, the series is shorter, the average is too high and seasonal or intermittent patterns are distorted. Zero-fill before any statistical modelling.

#### Should demand history use orders or shipments?

**Answer:** Orders by requested date approximate demand better, because shipments are capped by supply. Shipments are easier to get and reconcile to finance. If you use shipments, flag periods with stock-outs and consider adjusting them.

#### Does this query work on SAP or Oracle tables?

**Answer:** The names are generic. The logic of grouping by item and month applies to any ERP, but you must map to the real tables or views, and review date functions and any client or organization filters for your system.

#### How do I get weekly history instead?

**Answer:** Replace the month truncation with a week truncation, for example DATE_TRUNC with the week unit in PostgreSQL, and change the generated series interval to one week. Keep the week start convention consistent.

## Search terms

sql demand history query, monthly sales history by item sql, demand planning sql, fill missing months with zero sql, forecast history extract erp, shipment history query, generate series monthly demand, sql server monthly demand by item

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json