inv-table.png
hi ,
I have two Tables,
1) Inventory table INV_TABLE : Having fields: material_no, quantity, user_id and location_no
2) Delivery Note Table DNOTE_TABLE: Having fields: DNOTE_NO, material_no, quantity, user_id, and many more.
Now we have a requirement, that we have to generate a stock report that has
Material_No, User_ID, Total_Available_Qty.
Here Total Available quantity is calculated by subtracting the Stock Table Quantity with Delivery Note table quantity
Can you assist in forming the select query...
I used Left Join but I am not getting proper count as well as negative values are populated in total_available_Qty. My Query is as follows
SELECT DISTINCT INV.material_no,INV.USER_ID, (SUM(INV.quantity) - SUM(PICK.quantity)) AS TOT_QTY
FROM INV_TABLE INV LEFT JOIN
DNOTE_TABLE PICK ON INV.USER_ID = PICK.USER_ID AND INV.material_no = PICK.material_no
WHERE INV.quantity NOT IN ('0')
GROUP BY INV.material_no, INV.USER_ID ORDER BY TOT_QTY
That is, basically here as shown in the image, all the inventory table records should be fetched leaving the delivery note table records.
Kindly assist.
Regards
Govardan
inv-table.png
