I have to join 2 tables in order to find out the Products that have Stock in danger to expire based on current orders.
*Each product can have multiple stock locations,
*Each product can have multiple orders/day,
*One Product can be present in the Stock_table and absent from Orders_table & vice versa or present in both tables
Stock_table
ProductID(VARCHAR) |StockLocation(VARCHAR) |Stock(NUMBER) |Stock_Expiration_Date(DATE-YYYY-MM-DD)
Meatballs |Warehouse | 100 |2022-04-01
Meatballs |Fridge | 10 |2022-04-21
Milk |Fridge | 5 |2021-12-24
Orders_table
ProductID(VARCHAR) | Order_ID(VARCHAR) | Quantity(NUMBER) | Order_Date(DATE-YYYY-MM-DD)
Meatballs | x1 | 50 | 2022-01-19
Meatballs | x2 | 200 | 2023-09-22
Milk | x1 | 100 | 2021-10-22
60(110-50) meatballs will expire before the next order;
What have i tried:
Created a Product table with the unique values from Stock_Table and Orders_Table (Union and drop duplicates)
Product_Table
ProductID(VARCHAR)
Meatballs
Milk
Joined the Product_Table to the Stock_Table and Orders_Table based on ProductID with a left join
on
Product_Table.ProductID = Stock_Table.ProductID
and
Product_Table.ProductID = Orders_Table.ProductID
Created a Calendar table with the unique date values from Stock_Table and Orders_Table
Calendar_Table
Date_id
2022-04-01
2022-04-21
2021-12-24
2022-01-19
2023-09-22
2021-10-22
Got stuck here..