Thanks Hilmar. I appreciate it.
>OK, glad to help.
>
>When saving information (prices, in our case), I often use "delta compression", meaning that information is only saved when there actually are changes. I.e., information is saved every week, but if there are no changes from the previous week, no information is saved, since the information from the previous week is still valid.
>
>Here is one of the functions (I have historical data in several places). Please note that any solution based on SEEK - such as this one - is not scalable to a client-server scenario, and will therefore need adaptations.
>
>
>FUNCTION GetLiqPrice(tnArticle, tdDate)
> * Gets information from table LiquidationPrice
> * Information is stored on a weekly basis
> * returns only the primary key
> * other keys are then fetched with a SELECT - SQL
> local lnSelect, lnReturnValue
> lnSelect = select()
> SelectOrOpen("LiquidationPrice", "Art_Week", "LiqPrice_GetLiqPrice")
> set near on
> seek bintoc(tnArticle) + dtos(tdDate)
> set near off
> if Article # tnArticle or PlanWeek # tdDate
> skip -1
> endif
> lnReturnValue = iif(Article = tnArticle, LiqPrice, 0)
> select (lnSelect)
> return lnReturnValue
>ENDFUNC
>
>********************************************************************************
>FUNCTION SelectOrOpen(tcTableName, tcOrder, tcAlias, tnBuffering)
> * Open a table. If it is already open, select it.
> * Parameters:
> * - tcTableName - The table to be opened
> * - tcOrder - index tag
> * - tcAlias - open with this alias
> * - tnBuffering - buffering parameter, normally 5, 3, or omitted
> if empty(tcAlias)
> tcAlias = tcTableName
> endif
> if used(tcAlias)
> select (tcAlias)
> else
> select 0
> use (tcTableName) alias (tcAlias) again
> endif
> if not empty(tcOrder)
> set order to (tcOrder)
> endif
> if VarType(tnBuffering) = "N"
> CursorSetProp("Buffering", tnBuffering)
> endif
>ENDFUNC
>>
>
>>>Hi Jill,
>>>
>>>Is the situation similar to the following? Starting on Date1, a product has a certain price; on and after Date2, the same product has a different price, etc., so you want to locate the nearest date, >= the desired date.
>>
>>yes, very similar:
>>
>>date and flag status
>>
>>date and beneficiary status
>>
>>date, amount1 and amount2
>>
>>THe dates in the table have to be contiguous. You've got me started, thanks SO much, Hilmar! I'll come back if i run into trouble....J
>>
>>>I have this situation; I just do SET NEAR ON, SEEK..., and SET NEAR OFF. Some additional adjustments are needed, like, if there is no exact match, go back to the previous record, and check whether you are not outside the range, i.e., you landed on another product, meaning that the desired product doesn't have a price for the desired date. Of course, I am assuming there is an index on the combination product + date.
>>>
>>>This requires some additional steps, but it is very fast.
>>>
>>>If several pieces of information are required, the UDF, instead of returning a price, can either:
>>>
>>>1. Have an additional parameter to state what field should be evaluated and returnd, or
>>>
>>>2. Especially if many fields have to be retrieved, just return the primary key, which can then easily be used in a SELECT to fetch additional fields.
>>>
>>>If this is what you need, I can search for my lookup functions, which you might then adapt.
>>>
>>>HTH,
>>>
>>>Hilmar.
>>>
>>>>Hi,
>>>>
>>>>I typically maintain history information with fields like:
>>>>
>>>>StartDate
>>>>EndDate
>>>>...values...
>>>>
>>>>When locating a record for a specific date, i can easily use Between( DateBeingSearchedFor, StartDate, EndDate ) in a LOCATE or SELECT statement.
>>>>
>>>>
>>>>Now i'm trying to think if i can easily just store the StartDate, i.e.,
>>>>
>>>>StartDate
>>>>...values...
>>>>
>>>>But i'm not coming up w/a quick and easy way to locate an entry for DateBeingSearchedFor.
>>>>
>>>>Any ideas? other than just doing a scan of the table?
>>>>
>>>>TIA, Jill
