lastpurcbyitem.sql

this is sample for traders

# Connection: LW-WS01
# Host: localhost
# Saved: 2008-03-17 12:50:50
# 
  
    SELECT (SUM(B.QTY*B.NETPRICE)/SUM(B.QTY)) as LASTCOST, B.TRNO,B.NETPRICE,B.ITEMCODE
    FROM PURC A, purcline B 
    WHERE 
    A.TRDATE=(SELECT MAX(TRDATE) FROM PURC,PURCLINE WHERE PURCLINE.ITEMCODE='ABB-SUR-T' AND PURCLINE.TRNO=PURC.TRNO) 
    AND B.TRNO=A.TRNO 
    AND B.ITEMCODE='ABB-SUR-T'
    GROUP BY ITEMCODE