Hi!
Table of product types contains desired quantities in shop
create temp table producttype (
productype char(10) primary key,
desired integer check ( desired> 0)
) on commit drop ;
insert into producttype values ( 'SHOE',3);
product table contains products and product types
create temp table product (
product char(20) primary key,
producttype char(10) references producttype
) on commit drop;
insert into product values ('SHOE1','SHOE'),('SHOE2','SHOE'),('SHOE3','SHOE'),('SHOE4','SHOE'),('SHOE5','SHOE');
Warehouse table contains quantities in warehouse to distribute
create temp table warehouse (
product char(20) primary key references product,
quantity integer check ( quantity> 0)
) on commit drop ;
insert into warehouse values ('SHOE1',50),('SHOE2',60),('SHOE3',70);
Shop table contains quantities in shop
create temp table shop (
product char(20) primary key references product,
quantity integer check ( quantity> 0)
) on commit drop;
insert into shop values ('SHOE4',1);
insert into shop values ('SHOE5',1);
sales table contains sold quantities. Most sold intems should moved from stock first
create temp table sales (
product char(20) primary key references product,
quantity integer check ( quantity> 0)
) on commit drop;
insert into sales values ('SHOE1',100),('SHOE2',200);
How to find product which should moved from warehouse to shop so that shop status will be increated to producttype.desired
quantity for products in warehouse ? Most sold products should moved first.
Only one product (quantity 1) should moved from each product code.
Using data abouve, there should be 3 shoes (producttype.desired) in shop but are only 2 (sum(shop.quantity) for shoes).
Most sold shoe in warehouse is SHOE2
So SHOE2 should be moved to shop from warehouse.
How to find products which should moved ?
Can some SELECT with window function used for this ?
PostgreSQL 9.3.5 is used.
Andrus.
Posted also in https://stackoverflow.com/questions/63433824/how-to-distribute-products-to-shop-by-amount-of-sales
