Looping again - Monday deadline 2

[EKC]

Hi,
I need help solving this problem:
I need to go trough the table and for OrderID and OrderLine
(I pass as a function arguments) loop as long as Sum of RelQTY is Less or equal then QTY(I pass as a third argument) and when this happend ,returnd value of RelDate field of a record.Ex:
OrderID OrderLine RelQTY RelDate
1001 1 5 5/4/01
1001 1 10 6/5/01
1001 1 5 6/10/01
So if i pass(1001,1,16) I loop trough table till Sum(RelQTY)<=16 and since that is on second record I need to return RelDate of 6/5/01
thanks for any idea ,
Lyn

 

[Walid]

I will assume that your table has an index on OrderID and
OrderLine (a very common index in the order line file)

ldMyDate={}
ldMyDate=lfGetDate(OrderId,OrderLine,StopCond )
FUNCTION lfGetDate
PARAMETERS tOrderId,tOrderLine,tStopCond
ldLastDate={}
IF SEEK(<tOrderId,tOrderLine>) && This is not ligel FOX code
                  && Modify it to match your index exp
  lnSum=0 
  SCAN REST WHILE OrderId=tOrderId AND OrderLine=tOrderLine
    lnSum=lnSum+RelQTY
     
    IF lnSum > tStopCond
      EXIT
    ELSE
      ldLastDate=RelDate
    ENDIF          
  ENDSCAN
ELSE
  WAIT WIND &quot;Order ID or Line not found.&quot; 
ENDIF
RETURN(ldLastDate)

Walid Magd
Engwam@Hotmail.com

 

[chpicker]

Well, it may not be the best way, but I couldn't find a single command to do what you're looking for. If someone else can offer a better way to do it, I won't be surprised...but this should at least work for you. You should, of course, modify this code to match your indexes.


function GetQty(nOrderID,nOrderLine,nQtyToGet)
lnQty=0
ldReturnDate={^1980-1-1} && default value in case first Qty is too high
ValToSeek=nOrderID+nOrderLine && Make this match your index syntax
SELECT OrderTable
SET ORDER TO IDplusLine && whatever your index name is
SEEK ValToSeek
DO WHILE OrderID=nOrderID and OrderLine=nOrderLine and !EOF()
lnQty=lnQty+RelQty
IF lnQty>nQtyToGet
EXIT
ENDIF
ldReturnDate=RelDate
SKIP
ENDDO
RETURN ldReturnDate

 

[ramani]

***************************************************
PROCEDURE getRelDate && or FUNCTION getRelDate
PARAMETERS myOrderId, MyOrderLine, MyQty

LOCAL ldDate, lcAlias, lnQty
ldDate = CTOD(&quot;01/01/2001&quot && or whatever
lcAlias = ALIAS()
lnQty = 0

** Check for in parameters if necessary
** IF PARAMETERS() < 3
** WAIT WINDOW &quot;PARAMETERS ENTRY ERROR&quot; NOWAIT
** RETURN ldDate
** ENDIF

SELECT myAlias && alias having the Orderid,relDate etc
** assumed index set to OrderId+OrderLine and
** these are charater fields. Or else suitably change ...

SCAN FOR (OrderId+OrderLine == myOrderId+myOrderLine) ;
.AND. lnQty <= myQty
lnQty = lnQty + RelQty
ldDate = RelDate
ENDSCAN

SELECT (lcAlias)

RETURN ldDate
***************************************************
Hope the above helps you.
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[ramani]

Note in the above if the index is OrderId+OrderLine

SCAN FOR (OrderId+OrderLine == myOrderId+myOrderLine} is rushmore optimised ...
(and comparison orderid=myOrderid is not optimised.. since the index value is a compound of two fields.). I just thought I should specify the differences in the construct of the code which will go a long way in speeding up results. ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[EKC]

Thanks guys,
I really appreciated yours help
Lyn