Hai,
I have a query as shown below, BUT it only show current position. Is it possible to show value for specific period for example value for period 02/01/2014 til 28/02/14. It will looks like inventory audit report that can filtered by date.
SELECT T0."ItemCode",T1."ItemName", SUBSTRING(T0."WhsCode",3,2) AS "DEPT",SUM(T0."OnHand") AS "INSTOCK", SUM(T0."IsCommited") AS "COMMITED", SUM(T0."OnOrder") AS "ORDERED", T0."AvgPrice", SUM(T0."OnHand"*T0."AvgPrice") AS "INVT VALUE"
FROM OITW T0
INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
WHERE T0."OnHand" >0 AND T0."WhsCode" <>'01' AND T1."validFor" = 'Y'
GROUP BY T0."ItemCode", T0."AvgPrice", SUBSTRING(T0."WhsCode",3,2),T1."ItemName"
ORDER BY SUBSTRING(T0."WhsCode",3,2),T0."ItemCode"
Thanks