I have table name with Products (ItemId, GName, BName, OnHand) and StockInventory (ItemCode, BatchNo, ExpDate, PurchaseQty, PurchaseRate, SelRate, Remain)
My expected Output is
| ItemCode |
BatchNo |
ExpDate |
SelRate |
Remain |
| 1 |
dfdd |
25-10-2019 |
70 |
0 |
| 2 |
4548 |
20-05-2021 |
20 |
10 |
| 3 |
8788 |
21-09-2022 |
40 |
10 |
| 4 |
665FE |
21-01-2020 |
3 |
10 |
| 5 |
788XF |
15-01-2023 |
5 |
10 |
| 1 |
SEEEXR |
10-02-2023 |
70 |
0 |
| 1 |
LOP45 |
10-02-2022 |
70 |
20 |
DECLARE @Product AS TABLE(itemid INT,BName VARCHAR(50), OnHand INT)
DECLARE @StockInventory AS TABLE (ItemCode INT, BatchNo VARCHAR(50), ExpDate DATETIME, SelRate DECIMAL(18,2), Remain INT)
INSERT INTO @Product VALUES(1,'paracetamol',50)
INSERT INTO @Product VALUES(2,'koflet syrup',100)
INSERT INTO @Product VALUES(3,'Clotrimazole',60)
INSERT INTO @Product VALUES(4,'Ezy Tab',30)
INSERT INTO @Product VALUES(5,'MontekLc',70)
INSERT INTO @StockInventory VALUES(1, 'dfdd', '2019-10-25',70.00,10)
INSERT INTO @StockInventory VALUES(2, '4548', '2021-05-20',20.00,10)
INSERT INTO @StockInventory VALUES(3,'8788', '2022-09-21',40.00,10)
INSERT INTO @StockInventory VALUES(4, '665FE', '2020-01-21',3.00,10)
INSERT INTO @StockInventory VALUES(5, '788XF', '2023-01-15',5.00,10)
INSERT INTO @StockInventory VALUES(1, 'SEEEXR', '2023-02-10',70.00,20)
INSERT INTO @StockInventory VALUES(1, 'LOP45', '2022-02-10',70.00,20)
DECLARE @Sales AS TABLE (PCode INT, SRate DECIMAL(18,2), TQty INT)
INSERT INTO @Sales VALUES(1, 70.00, 30)
I want deduct quantity where the product has short expiry first, then the next one when any sales is made.
For eg
I got order for product 30pcs which has 50 in stock lets say "paracetamol" (itemId 1) . Among that 10 get expire in October this year, 20 get expire in February 2023 and 20 get expire in February 2022
So I am making sale for the item "paracetamol" order (30pcs) and I have to deduct the quantity from the stock
For this order i am selling 10 from the October this year expiry and 20 from the February 2022 expiry
Now I to update the table stockinventory for the itemCode 1 Remain filed as October this year 0 and February 2022 expiry 0 as I have sold out this items
Update Product table OnHand = 20 for that itemId 1 (paracetamol)
