on 03-10-2020 11:47 AM
hi Experts,
We have two Tables, one is Inventory Table (which has stock details ) and another Delivery Note Table ( which has goods delivered details ), from these two tables i have to fetch records to show a consolidated stock report avoiding duplicates .
As shown in the below table, In inventory we have 6 line items with different dates & delivery note table has 2 line items
The consolidated Stock Report is obtained by subtracting the Total Inventory Qty of each part number from the total delivered quantity from the Delivery Note Table.
we are using a query as shown below which was suggested in How to write Select Query that involves Subtraction of Two columns of different Tables by venkateswaran.k
SELECT PART_NO, USER_ID, SUM(QTY - PICK_QTY) AS TOTAL_QTY
FROM ( SELECT INV.PART_NO, INV.USER_ID ,INV.QTY,PICK.PICK_QTY FROM INVENTORY_TABLE INV LEFT JOIN DELIVERY_NOTE_TABLE PICK
ON INV.USER_ID = PICK.USER_ID
AND INV.PART_NO = PICK.PART_NO
WHERE INV.QTY > 0
) GROUP BY PART_NO, USER_ID
but still we are getting few negative values in Consolidated Stock Report Quantity kindly help
We are using SAP Enterprise Portal Data base migrated to HANA System.
Regards
Govardan Raj
Dear Govardhan
It looks like thee are Partno in not in invoice table but in delivery table.. (this could be the cause of negative). - Kindly verify this for those items coming negative. and update me
else change the query as below and check
Option 1
Instead of LEFT JOIN - you use INNER JOIN
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.