Skip to Content
0
Apr 14, 2014 at 09:52 AM

Inventory Value By Period

32 Views

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