Path: utzoo!attcan!uunet!samsung!zaphod.mps.ohio-state.edu!sol.ctr.columbia.edu!cica!iuvax!maytag!xenitec!timk
From: timk@xenitec.on.ca (Tim Kuehn)
Newsgroups: comp.databases
Subject: Re: Database structure problem (Fox/dBASE)
Summary: No easy way, gotta do it the 'manual' way
Keywords: dBase FoxBASE structure relation index
Message-ID: <1990Mar21.151016.4721@xenitec.on.ca>
Date: 21 Mar 90 15:10:16 GMT
References: <14715@phoenix.Princeton.EDU>
Reply-To: timk@xenitec.UUCP (Tim Kuehn)
Organization: TDK Consulting Services, Kitchener, ON, Canada
Lines: 90

In article <14715@phoenix.Princeton.EDU> englandr@phoenix.Princeton.EDU (Scott Englander) writes:
>Here's a problem that many of you can no doubt help me with.  I have a
>table of daily meter readings for several meters, and must somehow make
>calculations based on those readings and constants for each meter (which
>change occasionally).
>
>For illustration, let's make an analogy to a fruit company.  The company

...description deleted...

>I thought of having a single price table with these fields:
>
>eff_date
>fruit_name
>price
>
>where eff_date is the date when the price becomes effective, and
>fruit_name corresponds to the name of the field in the quantities sold
>table.  I may have to occasionally go back to old records, revise the
>prices, and recalculate costs.  So i need to store a time history of
>each price.  In calculating daily costs for each fruit, i'd like to
>use a relation to point to the price table and find the most recent
>price (i.e. for that date which is equal to or less than the date of the
>current record).  The problem is that even if you could figure out some
>way to index on a combination of date and name, the pointer would go to
>the end of the file when the current date falls on a day when the price
>did not change.

Quite true. This is one problem you'll have to do manually since there's 
no direct and matching one-one or one-many relation here. So you'll have to 
do the locations manually. Assuming we have two databases, this is the way
I'd do it:

First database with the meter (er..fruit) readings :-) (call it db1)
fruit_name
date
reading

Second database with the price history: (Call it db2)
fruit_name
price_date 
price


Constructing an index on the second database like so:
index on fruit_name+dtoc(price_date,1)

Note: the dtoc(x,1) gives you a string representation of the date in 
yyyymmdd format. But I don't have my docs here to double check it, so 
make sure that's what it does.

When you want to find the price for the record you're looking at in 
db1 you would do the following in db2:

seek db1->fruit_name+dtoc(db1->date,1)

of course, odds are you're not going to find the record you're looking for
in db2, however, we can retrieve the next record *past* the one we're looking
for with the recno(0) function. So you're next statements will be:

goto recno(0)
skip -1

which should give you the pointer to the price change you're after. 

An alternative way would be to have two indexes on db2 - like so:
Index 1:	fruit_name unique
index 2:	fruit_name+dtoc(price_date,1)

then you could go 

seek db1->fruit_name
set order to 2
locate rest for 	(db2->fruit_name = db1->fruit_name) ;
		.and. 	(db2->date > db1->date) ;
	    while 	(db2->fruit = db1->fruit) .and. .not. eof()
skip -1

This should leave the file pointer at record you're after too, albeit
it's a bit more kludgy, it is an explicit statement of what's assumed to 
be going on in the first example, and hence possibly a bit "safer".

Hope this helps you (and anyone else out there that may be watching)!

------------------------------------------------------------------------------
Timothy D. Kuehn, TDK Consulting Services, 871 Victoria St. North
Kitchener, Ontario, Canada N2B 3S4  		voice: (519)-741-3623
DOS/Xenix - SW/HW. uC, uP, RDBMS.  		timk@xenitec.on.ca
Quality work guaranteed - period. 		!watmath!maytag!xenitec!timk
------------------------------------------------------------------------------