By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ITEM (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN ,
QUANTITY_ON_HAND DECIMAL(5, 2) NOT HIDDEN ,
PRIMARY KEY (ITEM_NUMBER) ) ;
CREATE TABLE ORDER_ITEM_REQUIREMENTS (
ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN ,
DATE_ORDERED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL NOT HIDDEN ,
QUANTITY DECIMAL(4, 2) NOT NULL NOT HIDDEN ,
COST_PER DECIMAL(10, 2) NOT NULL NOT HIDDEN ,
PRIMARY KEY (DATE_ORDERED, ITEM_NUMBER) ,
FOREIGN KEY (ITEM_NUMBER) REFERENCES ITEM (ITEM_NUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION )
;
INSERT INTO ITEM (ITEM_NUMBER, QUANTITY_ON_HAND) VALUES ('APPLE', 5);
INSERT INTO ORDER_ITEM_REQUIREMENTS (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 1.23);
INSERT INTO ORDER_ITEM_REQUIREMENTS (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 2.34);
INSERT INTO ORDER_ITEM_REQUIREMENTS (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 5.55);
with t( item_number, num_shipments, qty_sum, cost_sum, qty_on_hand) as (
select
i.item_number
,sum(1) over (
partition by o.item_number order by o.date_ordered
)
,sum(o.quantity) over (
partition by o.item_number order by o.date_ordered
)
,sum(o.quantity*o.cost_per) over (
partition by o.item_number order by o.date_ordered
)
, i.quantity_on_hand
from
item i
inner join
order_item_requirements o
on i.item_number = o.item_number
)
select
item_number
,max(num_shipments) num_shipments
,max(qty_sum) qty_sum
,max(cost_sum) cost_sum
from t where qty_sum <= qty_on_hand
group by item_number
| ITEM_NUMBER | NUM_SHIPMENTS | QTY_SUM | COST_SUM |
|---|---|---|---|
| APPLE | 2 | 4.00 | 7.1400 |