>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
>
>
>Thanks,
>Sam
Hi Sam,
Welcome to UT:) (soecify your version or else you'd get VFP9 specific SQL mostly - I know you've VFP7).
Your code sound to translate:
CREATE CURSOR VendorPerf (VendorID c(5), VenName c(25), NumReceipts I, QtyRecv N(10), QtyRej N(6), PerRej N(4,2), OnTime I)
SELECT ;
vendorname,;
vendorID,;
cnt(*) as numRect, ;
sum(qtyrej) as rejQty, ;
sum(qtyrecv) as recvQty ;
FROM rejects ;
WHERE daterecv BETWEEN StartDate AND EndDate ;
GROUP BY 1,2 ;
ORDER BY 1 ;
INTO CURSOR OrderRejects
scan
INSERT INTO VendorPerf ;
(VendorID,VenName,NumReceipts,QtyRecv,QtyRej,PerRej,OnTime) ;
VALUES ;
(OrderRejects.VendorID,;
OrderRejects.VendorName,;
OrderRejects.NumRect,;
OrderRejects.RecvQty,;
OrderRejects.RejQty,;
ROUND(OrderRejects.RejQty/OrderRejects.RecvQty*100,2),0)
endscan
or:
SELECT ;
vendorID as VenID,;
vendorname,;
cnt(*) as numReceipts, ;
sum(qtyrej) as QtyRej, ;
sum(qtyrecv) as QtyRecv ;
FROM rejects ;
WHERE daterecv BETWEEN StartDate AND EndDate ;
GROUP BY 1,2 ;
ORDER BY 1 ;
INTO CURSOR OrderRejects
SELECT *, ;
ROUND(QtyRej/QtyRecv*100,2) as PerRej,;
0 as Ontime ;
FROM OrderRejects ;
INTO CURSOR VendorPerf
However VendorPerf cursor might never be needed for a report and calculation could be done directly on report.
Cetin