Re: Finding first stock deficiency for article

Before I go into detail about my errors, am I on the right track or do I need to step out and try a completely different approach? <<

First, let's clean up your DDL a little bit. Plural or collective
nouns for table names; add NOT NULLs and DRI; get a real key instead
of a vague magical universal "id" on the tables. How about a ticket
number when more than one delivery or inventory withdrawal is made on
the same day?

CREATE TABLE Articles
(article_nbr CHAR(9) NOT NULL PRIMARY KEY,
stock_level INTEGER NOT NULL);
INSERT INTO Articles VALUES ('a', 5);
INSERT INTO Articles VALUES ('b', 0);

CREATE TABLE Purchases
(article_nbr CHAR(9) NOT NULL,
delivery_date DATE NOT NULL,
delivery_tkt INTEGER DEFAULT 1 NOT NULL
CHECK (delivery_tkt > 0),
purchase_qty INTEGER NOT NULL,
PRIMARY KEY (article_nbr, delivery_date, delivery_tkt));

INSERT INTO Purchases VALUES ('b', '2010-01-02', 1, 2);
INSERT INTO Purchases VALUES ('a', '2010-01-03', 2, 20);
INSERT INTO Purchases VALUES ('a', '2010-01-05', 1, 10);

CREATE TABLE Usages
(article_nbr CHAR(9) NOT NULL
REFERENCES Articles (article_nbr),
usage_date DATE NOT NULL,
usage_tkt INTEGER DEFAULT 1 NOT NULL
CHECK (usage_tkt > 0),
usage_qty INTEGER NOT NULL,
PRIMARY KEY (article_nbr, usage_date, usage_tkt));

INSERT INTO Usages VALUES ('b', '2010-01-02', 1, 2);
INSERT INTO Usages VALUES ('a', '2010-01-01', 2, 5);
INSERT INTO Usages VALUES ('a', '2010-01-04', 1, 17);
INSERT INTO Usages VALUES ('a', '2010-01-04', 2, 6);

We can do a UNION to get everything together. On top of that, we do
the sum to get the daily net change. Finally, the outer level uses
the new OLAP stuff to get a running total.

SELECT article_nbr, event_date, daily_change_qty,
SUM(daily_change_qty)
OVER (PARTITION BY article_nbr
ORDER BY event_date
ROWS UNBOUNDED PRECEDING) AS running_tot
FROM (SELECT article_nbr, event_date, SUM(event_qty) AS
daily_change_qty
FROM (SELECT article_nbr, delivery_date, +purchase_qty
FROM Purchases
UNION ALL
SELECT article_nbr, usage_date, -usage_qty
FROM Usages
UNION ALL
SELECT article_nbr, '1900-01-01', stock_level -- dummy date
FROM Articles)
AS Events (article_nbr, event_date, event_qty)
GROUP BY article_nbr, event_date)
AS DailyChanges (article_nbr, event_date, daily_change_qty);
.