Well, my morning and most of my afternoon got very busy. Had some time now for you.
I took your first query and altered it to create the NET amount you required. I had to do some odd
select code in it to make it work. Here is the query:
SELECT M.BEGINNING_INVENTORY, S.MATERIAL_CODE, S.MATERIAL_NAME, SUM(S.IMPORTATION_QUANTITY) AS SUM_IMPORTATION, SUM(S.EXPORTATION_QUANTITY) AS SUM_EXPORTATION, (M.BEGINNING_INVENTORY + SUM(S.IMPORTATION_QUANTITY) - SUM(S.EXPORTATION_QUANTITY)) AS NET FROM STOCK_INFORMATION AS S LEFT JOIN MATERIALS AS M ON M.MATERIAL_CODE = S.MATERIAL_CODE WHERE S.MATERIAL_NAME != '' GROUP BY M.MATERIAL_NAME
As you see, I removed the unneeded AS and added in the calc for the NET total. The results was this:
I think that is what you wanted… Next, I took the second query and attempted to merge it with the first.
From your posts, you just want to add in the NET from the first query into this second query. Correct?
I basically, just added in only the needed parts of the first query to create the NET again. You version was
messy. I cleaned it up. Here is the number 2 query cleaned up without the NET in it.
SELECT SUM(RESULT) AS FINAL, MATERIAL_NAME, UNIT, MATERIAL_TYPE FROM( SELECT P.PRODUCT_NAME, SUM((B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY)) AS RESULT, B.MATERIAL_NAME, B.ACTUAL_QUANTITY, B.PRODUCT_CODE, B.UNIT, B.MATERIAL_TYPE, B.BOM_NAME FROM BOM AS B LEFT JOIN PRODUCTION_PLAN AS P ON P.PRODUCT_CODE = B.PRODUCT_CODE AND P.BOM_NAME = B.BOM_NAME WHERE MATERIAL_NAME !='' GROUP BY MATERIAL_NAME, PRODUCT_CODE ) AS RESULT GROUP BY MATERIAL_NAME
Now, here is the same version with the NET added in. I just added another JOIN with the previous query
in place. Here it is:
Well, it seems I am not that good with queries… I was able to get the query to see ALL of the data, but,
the JOIN’s were not working as they should. Either they pulled in all the data and did not sum it correct
or they got the wrong group-by lists. I have had a lot of work coming in. But, will have time this weekend
to try it again for you. Sorry for the delays…