I have platform to extract data from sql tables and so far all queries were generated by simple drag and drop tool. Now I am trying to change query manually, but it's not working as expected...
Can you take a look?
Query delivered by generator:
SELECT
repo.MAT.MAT_A_COD,
inventory.INV.MRP_RQMT_DT,
SUM(inventory.INV.MRP_AVL_QTY)
FROM
repo.MAT RIGHT OUTER JOIN inventory.INV ON (inventory.INV.MRP_MAT_A_FK=repo.MAT.MAT_A_PK)
WHERE
( inventory.INV.MRP_COMPANY_COD IN ('01','02') )
GROUP BY
1,
2
Results:
Material A | 2020.01.01 | 100
Material A | 2020.01.02 | 200
Material A | 2020.01.03 | 300
Material B | 2020.01.01 | 10
Material B | 2020.01.02 | 0
What I am looking for: only values for the latest date for each material.
Material A | 2020.01.03 | 300
Material B | 2020.01.02 | 0
I tried with MAX(inventory.INV.MRP_RQMT_DT), but no success. Any help is appreciated!
You can try the below -
SELECT
repo.MAT.MAT_A_COD,
inventory.INV.MRP_RQMT_DT,
SUM(inventory.INV.MRP_AVL_QTY)
FROM
repo.MAT RIGHT OUTER JOIN inventory.INV ON inventory.INV.MRP_MAT_A_FK=repo.MAT.MAT_A_PK
WHERE
inventory.INV.MRP_COMPANY_COD IN ('01','02') and inventory.INV.MRP_RQMT_DT=(select max(inventory.INV.MRP_RQMT_DT) from inventory.INV inv1 where inventory.INV.MRP_MAT_A_FK=inv1.MRP_MAT_A_FK)
GROUP BY 1, 2
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments