create table Input (PropID I, Date D, Source C(1)) create table OutPut (TotRooms N(2), BedRooms N(2), PrSlDate D) create table TranMstr (PropID I, Date D, Source C(1), Price I, Book I, Page I) index on str(PropId,9)+dtos(Date) tag TrxnHist close all use Input alias X in 0 use TranMstr in 0 use OutPut in 0 alias Y local i for i=1 to 20 PropId= m.i Source = 'S' date = date()- m.i Price = m.i*10000 Book = m.i+1000 Page = m.i+100 insert into X from memvar insert into TranMstr from memvar date = date()- m.i - 365 Price = m.i*1000 Book = m.i+5000 Page = m.i+500 insert into TranMstr from memvar && Second record in TranMstr - prev. sale next do Suspect_Replace close all ************************** function Suspect_Replace select X set step on scan append blank in Y replace Y.TotRooms with 100000, Y.BedRooms with 10000, ; Y.PrSlDate with GetPrSlInfo(PROPID,SOURCE,DATE,"Date") endscan ******************************************************************************** function GetPrSlInfo * Description.......: Function returns the previous sale info if it exists * Calling Samples...: GetPrSlInfo(PROPID,SOURCE,DATE,"Date","C",8) * Parameter List....: tnPropid, tcSource, tdDate, tcFieldName, tcRetType (Optional), tnNumChar (Optional) * Created by........: Nadya Nosonovsky 08/15/01 12:04:59 PM * Modified by.......: Nadya Nosonovsky 08/15/01 12:05:02 PM ******************************************************************** lparameter tnPropId, tcSource, tdDate, tcFieldName, tcRetType, tnNumChar, lnRecno, lnReccount local lnSelect, lVar lnSelect=select() lnRecno = recno() && Assuming we're sitting on the correct file lnReccount = reccount() if !used('curPrevSlInfo') select 0 create cursor curPrevSlInfo (RecNum I, date D, Price I, Book I, page I) append blank endif if curPrevSlInfo.RecNum <> m.lnRecno replace RecNum with m.lnRecno in curPrevSlInfo * lVar=CreateEmptyVar(m.tcFieldName,'TranMstr') if m.tcSource = "S" and seek(str(m.tnPropId,9)+dtos(m.tdDate),"TranMstr","TrxnHist") select TranMstr set order to TrxnHist descending browse set step on if !eof() skip if TranMstr.PropID = m.tnPropId do while TranMstr.PropID = m.tnPropId if TranMstr.source = "S" and TranMstr.date < m.tdDate replace Date with TranMstr.Date, ; Price with TranMstr.Price, ; Book with TranMstr.Book, ; Page with TranMstr.Page in curPrevSlInfo *!* scatter memvar *!* insert into curPrevSlInfo from memvar exit endif if !eof() skip else exit endif enddo endif endif endif endif lVar = evaluate('curPrevSlInfo.'+ m.tcFieldName) if m.lnRecno=m.lnReccount && Last record use in curPrevSlInfo endif select (m.lnSelect) if !empty(m.tcRetType) if vartype(m.lVar)<>m.tcRetType return ConvertType(m.lVar,m.tcRetType,m.tnNumChar) endif else return m.lVar endif>Nadya
>CREATE TABLE SPEED.DBF FREE ; > (Spd_ID I, ; > Spd_Char C(10), ; > Spd_Date D, ; > Spd_DT T) >LOCAL lnI, lnX, Spd_ID, Spd_Char, Spd_Date, Spd_DateTime >FOR lnI = 1 TO 1000000 > SPD_ID = m.lnI > SPD_CHAR = STR(m.lnI) > *This will give me a set of records > *with the same date / date time. > LNX = m.lnI/100 > SPD_DATE = {^2000-01-01}+m.lnX > SPD_DT = {^2000-01-01 00:00:00}+m.lnX > INSERT INTO SPEED FROM MEMVAR > IF MOD(m.lnI,1000) = 0 > WAIT WINDOW STR(m.lnI) NOWAIT > ENDIF >ENDFOR >INDEX ON SPD_ID TAG SPD_ID >INDEX ON SPD_CHAR TAG SPD_CHAR >INDEX ON SPD_DATE TAG SPD_DATE >INDEX ON SPD_DT TAG SPD_DT>
>USE speed >replace ALL spd_char WITH myudf(spd_id) >RETURN > >FUNCTION myudf >LPARAMETERS tiID >LOCAL lnSelect >lnSelect = SELECT() >SELECT 0 >IF !used("mylog") > CREATE CURSOR mylog (log_id n(2)) >ENDIF >APPEND BLANK IN mylog >replace log_id WITH m.tiID IN mylog >SELECT (lnSelect) >RETURN STR(m.tiID)>