cancel
Showing results for 
Search instead for 
Did you mean: 

Select Query to Obtain Consolidated Report From Two Different Tables.

govardan_raj
Contributor
0 Kudos

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

venkateswaran_k
Active Contributor
0 Kudos

Dear Govindaraj

Please attach the screenshot of where you see negative value

This will help to understand the issue

Regards,

venkat

govardan_raj
Contributor
0 Kudos

Hi Venkat,

Please find the below image , where resulting table obtained from query tool on executing the query you had suggested.

The first line item has quantity of -1 and last one has quantity 0 .

Regards

Govardan Raj

former_member188958
Active Contributor

I'm confused on some issues in the problem description.

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

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?

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

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:

SELECT 
i.material_no as "material", 
       i.quantity as "invoiced" ,
       d.quantity as "picked"
FROM i
LEFT JOIN  d
ON 
i.material_no = d.material_no 
ANDi.material_no = d.material_no 
WHERE  i.quantity > 0 

 material    invoiced    picked
 ----------- ----------- -----------
         100          10           5
         101          10          40    ---> 10-40 = -30
         102          20        NULL
         103          30        NULL
         105          40        NULL
         101          50          40 ---> 50-40 = 10. 
sum: (-30 + 10 = -20)!!!


Anyway, I think I would try along these lines (written for Adaptive Server Enterprise TSQL, may need a tweaking)

select 
inv.material_no, 
inv.qty - coalesce(del.qty,0)
from
(select material_no, sum(quantity) as qty 
from INVENTORY_TABLE group by material_no) inv
left join
(select material_no, sum(quantity) as qty 
from DELIVERY_NOTE_TABLE group by material_no) del
on inv.material_no = del.material_no
order by
inv_material_no

  material_no
 ----------- -----------
         100           5
         101          20
         102          20
         103          30
         105          40


(5 rows affected)




Cheers,

-bret

govardan_raj
Contributor
0 Kudos

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.

venkateswaran_k
Active Contributor

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

govardan_raj
Contributor
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

venkateswaran_k
Active Contributor
0 Kudos

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