>I have this free table and I am trying to calculate the % Rejected per manufacturer. I am trying to create a report where it will give me a summary per manufacturer per year. I have the vendor ID, Vendor Name, Number of Receipts, Quantity Received, Quantity Rejected, Percent Rejected and On Time fields in it. Somehow, my code does not properly calculate the Percent Rejected per vendor, but everything else is okay. Oh, I still need to work on my OnTime field for this. Please advice...
>
>
>* get the orders from free table - rejects.dbf
>SELECT * FROM rejects ;
>WHERE daterecv BETWEEN StartDate AND EndDate ;
>ORDER BY vendorname, daterecv ;
>INTO CURSOR OrderRejects
>
>* create a VendorPerfomance table to hold the report values
>CREATE CURSOR VendorPerf(VendorID c(5), VenName c(25), NumReceipts I, QtyRecv N(10), QtyRej N(6), PerRej N(4,2), OnTime I)
>
>SELECT OrderRejects
>
>DO WHILE !eof()
> Vendor = OrderRejects.vendorname
> VenID = OrderRejects.VendorID
> NumRect = 0
> RecvQty = 0
> RejQty = 0
> OT = 0
> DO WHILE Vendor = OrderRejects.vendorname
> NumRect = NumRect + 1
> RecvQty = OrderRejects.qtyrecv + RecvQty
> RejQty = OrderRejects.qtyrej + RejQty
> PerRej = Round((RejQty/RecvQty)*100,2)
> SELECT OrderMaster
> SET KEY TO OrderRejects.OrderNum
> LOCATE FOR LINNUM_10 = OrderRejects.LineNum and ;
> DELNUM_10 = OrderRejects.DelNum
> SELECT OrderRejects
> SKIP
> ENDDO
> INSERT INTO VendorPerf(VendorID,VenName,NumReceipts,QtyRecv,QtyRej,PerRej,OnTime) ;
> VALUES (VenID,Vendor,NumRect,RecvQty,RejQty,PerRej,OT)
>ENDDO
>
*******************************
Select vendorid,vendorname,cnt() as vendorcnt,sum(qtyrecv) as qtyrecv, ;
sum(qtyrej) as qtyrej,round(100*sum(qtyrej)/sum(qtyrecv),2) as perrej ;
from rejects group by 1
>
>Thanks,
>Sam
Edward Pikman
Independent Consultant