SQL Syntax that could be used for creating a listing by project of all Purchasing Variances for items purchased ON THE PO to the project.

 

SELECT costeach, ord_qty, part_no, schd_date;

      FROM poitschd, poitems;

      WHERE poitschd.uniqlnno = poitems.uniqlnno;

      order by parT_no,schd_date;

      into cursor getstuff

 

SELECT costeach, ord_qty, part_no, schd_date ;

FROM getstuff;

where schd_date IN (SELECT MAX(schd_date) FROM getstuff A1 WHERE A1.part_no=getstuff.part_no);

order BY part_no;

into CURSOR lastprice

 

Keep in mind that sometimes the last price may have been a panic buy and may cost more than the usual price. And bear in mind that this will be the last price on a PO whether or not it was received, and not necessarily what the actual price was if accounting modified the invoice in AP.