Multi-Period Planning - PackDB Examples

THE PROBLEM

A manufacturer plans production across 4 time periods with varying demand. Each period has a production cost, a per-unit holding cost for inventory, and a maximum production capacity. The key constraint is inventory balance: ending inventory in each period equals starting inventory plus production minus demand. The goal is to minimize total production and holding costs. This example shows integer decision variables (produce and inventory) with window function-based constraints (LAG for previous period's inventory).

SAMPLE DATA

CREATE TABLE Periods (
    period INTEGER, demand INTEGER, production_cost INTEGER,
    holding_cost INTEGER, max_capacity INTEGER
);
INSERT INTO Periods VALUES
    (1, 100, 10, 2, 200),
    (2, 150, 10, 2, 200),
    (3, 120, 10, 2, 200),
    (4, 180, 10, 2, 200);

THE QUERY

SELECT period, demand, production_cost, holding_cost, produce, inventory
FROM Periods
DECIDE produce, inventory
SUCH THAT
    produce IS INTEGER AND inventory IS INTEGER AND
    inventory >= 0 AND
    inventory = COALESCE(LAG(inventory) OVER (ORDER BY period), 0)
                + produce - demand AND
    produce <= max_capacity AND
    SUM(inventory) <= 0.2 * SUM(produce)
MINIMIZE SUM(produce * production_cost + inventory * holding_cost);

QUERY BREAKDOWN

1

DECIDE produce, inventory — Two integer decision variables per row: how much to produce and how much inventory to carry.

2

inventory >= 0 — Inventory cannot go negative.

3

inventory = COALESCE(LAG(inventory) OVER (ORDER BY period), 0) + produce - demand — Inventory balance: each period's ending inventory = previous inventory + production - demand. COALESCE handles period 1 (no previous inventory).

4

produce <= max_capacity — Production limited by factory capacity.

5

SUM(inventory) <= 0.2 * SUM(produce) — Total inventory held across all periods cannot exceed 20% of total production (lean manufacturing constraint).

6

MINIMIZE SUM(produce * production_cost + inventory * holding_cost) — Minimize combined production and holding costs.

RESULT

period	demand	produce	inventory
1	100	120	20
2	150	140	10
3	120	110	0
4	180	180	0

INTERPRETATION

The solver slightly overproduces in periods 1 and 2 (120 and 140 vs demand of 100 and 150), building small inventory buffers of 20 and 10 units. By period 3, inventory drops to 0, and period 4 produces exactly its demand (180). Total production cost: (120+140+110+180)×10 = $5,500. Total holding cost: (20+10+0+0)×2 = $60. Grand total: $5,560. The lean manufacturing constraint (total inventory ≤ 20% of total production = 110) is satisfied with total inventory of 30.