Skip to Content

Select Query to Obtain Consolidated Report From Two Different Tables.

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 Krishnamurthy

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

Add a comment
10|10000 characters needed characters exceeded

  • Dear Bret & Venkatesh,

    Thanks for your kind reply, sorry for late reply.

    1) Your sample code joins on user_id as well as part_no. Is this correct, items are always delivered by the same user who entered them into inventory or you are only reporting on inventory that was delivered by the same person who received it? (I'm making assumptions here as the meaning of the column). I suspect user_id shouldn't be part of the join

    Answer : We are joining based on User_id and part_no and this is correct, because we have multiple dealers who buy product from us and sell that to end customer.When Dealer buys product from us, he makes an entry at INVENTORY_TABLE, when he delivers that to an end customer, an entry is made to DELIVERY_NOTE_TABLE.

    2) What is the logic behind the date column? Must deliveries always have a date larger than equal to the date in the row of inventory? Do you want to catch any instances where that isn't true? When there are multiple rows for a part in inventory or multiple deliveries, what determines the date in the Consolidated report?

    Answer: yes you are right, when there are multiple deliveries, getting consolidate report based date column is not correct way.

    3) Why does the qty for part_no 102 decrease to 10 in your report when there were no deliveries of that part?

    Answer: Sorry it was wrongly updated, 10 qty of part_no 102 is delivered, which is not shown above.

    4) It may help you see where the negative numbers are coming from if you look at the raw data the join generates before aggregation, and do the subtraction and summation manually:

    Answer: ok


    In fact i would explain in detail,

    when a dealer U1000, purchases a part_no 102 of quantity 10 from us , we make an entry in INVENTORY_TABLE, with part_no, quantity purchased, date as well as the dealer name.

    As well as when he is delivering some of that to an end customer, then an entry is made into another table D2D_INVENTORY_TABLE . This table has an entry only when dealer delivers to an end customer, if he has not delivered then no entries . Assume if dealer has purchased 10 quantities of part_no 101 from us and has not yet sold to any end customer, then entry of 101 would be there at INVENTORY_TABLE, and in D2D_INVENTORY_TABLE we wont have any entry of 101.

    Both these tables with table feilds and entries are shown in snapshot,

    can we query these two alone and fetch the consolidated Inventory Report ?



    Kindly assist , Thanks in advance.

    Regards

    Govardan.

    inventory-tabl.png (11.9 kB)
  • Dear Govardhan

    Thank you for the detail explanation. By seeing the above scenario and the table snapshot,

    1) You said you received 10 Quantity - updated in inventory table - that is is okay

    2) The second table shows only the summary as opening and closing stock - as 7 now.

    3) There is no other table where you maintain detail of transactions. like IN / Out. (Is there any more table that you maintain the individual transaction as GR/GI)

    However, now how do you want your report format - Secondly is your negative value is resolved ?

    Kindly clarify this\

    Regards,

    Venkat

  • Dear Venkatesh,

    Yes we have another table named GRN_Table, where an entry is made when dealer receives a part_no from us.

    The Report format which i need is , like currently if i consider a dealer U1000, we want to know what are the part_no available at his storage location as well as how much quantity available.

    for example you consider a new dealer U2000 has purchased a part_no 104 from our side and of quantity = 100. In that 100 he has delivered 50 .

    Then i need inventory report to show that U2000 has 104 part_no of quantity 50.

    Regards

    Govardan

Assigned Tags

Related questions

1 Answer

  • Posted on Mar 10 at 12:29 PM
    -1

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.