How to write Select Query that involves Subtraction of Two columns of different Tables

[govardan_raj]

inv-table.pnghi ,

I have two Tables,

1) Inventory table INV_TABLE : Having feilds : material_no, quantity, user_id and location_no

2) Delivery Note Table DNOTE_TABLE :Having feilds : 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 Avaialble quantity is calculated by subtracting the Stock Table Quantity with Delivery Note table quantity

can u assist in forming the select query ...

i Used Left Join but 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 

I.e. basically here as shown in the image, all the inventory table records should be fetched leaving the deilvery note table records.

Kindly assist.

Regards

Govardan

[venkateswaran_k · Accepted solution]

HI

Try this

SELECT material, user, sum (invoiced - picked ) AS "TOTAL"
FROM  
( SELECT INV.material_no as "material", 
       INV.USER_ID as "user" ,
       INV.quantity as "invoiced" 
       PICK.quantity as "picked"
FROM INV_TABLE  as INV LEFT JOIN DNOTE_TABLE  as PICK ON INV.USER_ID = PICK.USER_ID AND INV.material_no = PICK.material_no 
WHERE  INV.quantity > 0 ) as "final"


GROUP BY material, user

[govardan_raj · Accepted solution]

Hi,

Thanks a lot for above query , but still am able to find few discrepancy, like am getting few negative values ... I have attached a pic here , where the consolidated stock report i actually need , can you please assist in fine tuning the query ?