englandr@phoenix.Princeton.EDU (Scott Englander) (03/21/90)
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
sells apples, oranges, and pears. Quantities sold are recorded on a
daily basis, as are the total costs of each fruit sold. Prices for each
fruit change occasionally, say once every 2 weeks or so, but not
according to a fixed schedule. So how do i store the pricing
information in a way that i can calculate costs daily for each fruit?
Assume there are too many fruits to give each one its own price table.
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.
This seems like a problem that others would have encountered before.
Can anyone help? (Please reply via e-mail, and i post a summary).
--
- Scotttimk@xenitec.on.ca (Tim Kuehn) (03/21/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 ------------------------------------------------------------------------------
englandr@phoenix.Princeton.EDU (Scott Louis Englander) (04/12/90)
A while back, i posted a query asking for advice on how to structure a
list of constants for use in calculating consumption based on meter
readings. I made the analogy to a fruit business, and got several
replies on what to do, which i've attached below.
Thanks to all who replied -- what you said was useful, although my
analogy was not very representative, and i should have just explained
the real situation (as boring as it is!). I'll do this here, before i
describe the solution i've settled on:
I have one table of inputs (mostly meter readings), and one table of
calculations (mostly consumption for each meter). Meter readings are
recorded for about 25 meters daily, and the consumption for each meter
for each day is calculated (along with equipment efficiency, etc.).
Meter constants, or multipliers, are used to calculate the consumption:
consumption = constant * (previous reading - current reading)
These change occasionally, and sometimes the consumption for a given
period of time must be recalculated when calibration errors are
discovered, or when meters were replaced, but the data entry people
don't find out until later.
To perform the calculations for any given day, i must look up the value
of the constants in effect for that day for each meter.
I was hoping to set up a "smart" structure for the constants table, so i
wouldn't have to go "locating" around for each value -- pointers would
do the work. Coburn (below) suggests such a scheme (for the fruit
business), but i was not able to make it work in this situation.
Here is the structure for the constants table i've settled on:
Field Field Name Type Width Dec
1 name Character 10
2 date Date 8
3 value Numeric 11 5
The file is indexed on CTOD("12/31/2100")-date
(that's Character To Date, for you non-dbase people).
This way, the most recent value for each constant is first. Then, to
get the value in effect for any given date, i use the following
function:
procedure ConstVal
* lookup value of given constant for given date
* Constants.dbf must be indexed on CTOD("12/31/2100")-date ;
so most recent is first.
parameters cname, thedate
lastdb = alias()
select &co
go top
locate for trim(name)=cname .and. date<=thedate
fnd = found()
temp = value
select &lastdb
if fnd
return temp
else
alert stop 2 "Invalid constant name: "+cname+"!"
return to master
endif
*end constval
Although this is time-consuming, it seems to work ok. Any comments are
appreciated. Below is a summary of replies to my original query.
====================================================================
Date: Wed, 21 Mar 90 13:45:32 +1000
From: ghm@ccadfa.cc.adfa.oz.au (Geoff Miller)
In comp.databases you write:
>I thought of having a single price table with these fields:
>eff_date
>fruit_name
>price
One option would be a separate table to record, for each fruit_name, the
date of the last price change. You then have immediate access to the record
in the price table. Alternatively, you could use one table for your current
prices, and put the records into the historical price table as a part of the
procedure for entering new prices. The first option requires an extra disc
access, but make make the procedure of updating the prices simpler.
I had a similar problem with a file registry system, where we have to record
the movement history for each file (ie, for each movement record the date and
the person taking the file). Fortunately this system was written using Prime
Information, which like Pick allows for multivalued variable-length fields.
One such field is used for the date, one for the person - what we do is
insert the new data at the beginning, so that the current values are always
the first ones. Unfortunately you don't have this option with dBase....
-----------------------------------
From: mwohl@engnet.ufl.edu
Date: 21 Mar 90 08:13 EST
I haven't used foxbase so this might not help at all, but Clipper has a command
set softseek on/off
which, if set on, will place the record pointer to the first occurence of the
key greater than the value you are seeking, if the value you are seeking is not
in the database. Thus all you would have to do is seek the value and if
the value you sought was not found, skip back one. I used this quite often,
and it seems like something the other vendors would pick up on, but I'm
not sure that they have. I hope this helps.
Mike
-------------------------------
Date: Wed, 21 Mar 90 14:14 CST
From: TODD@kuhub.cc.ukans.edu
I suggest you approach the problem from a different angle.
Instead of a table of prices showing basically the history of
price changes (which your not interested in given the problem
discription), use a INVENTORY database of items that contains only the
most current price (updates replace the old price with the new price).
Then use a second table, INVOICE or TRANSACT, that contains the items
identification code *AND* price. This record will "remember" the
price of the item when it was sold even if the INVENTORY database has
been modified with a new price.
Does this help?
--------------------------------------------------------------
Date: Fri, 23 Mar 90 16:36 CST
From: TODD@kuhub.cc.ukans.edu
> Well, actally i do need to maintain a price history, for cases when the
> prices last month are discovered to have been different, but not
> until this month, whereupon we will want to update last month's costs...
Uh, you lost me. Prices for items sold last month are recorded in the
invoice database with the items. For items sold this month, all you
care about is the current price. You have a price history of sorts (I
would never use it as such) by analysing the invoice database. You
can certainly calculate revenue by looking at the invoice database.
And "costs"?? This wasn't mentioned in the original problem. If you
care about costs, then create another field like price to keep track of the
cost of the item.
--------------------------------------------------------------
Date: Wed, 28 Mar 90 16:12:53 PST
From: mailrus!uunet!infmx!coburn (Dave Coburn)
I would suggest that you store the info in two tables, like shown:
Price Table:
price_id /* Unique ID number for this row */
fruit_name /* Obvious */
eff_date /* Same as yours */
price /* Cost of some known quantity, such as */
/* the case. */
Inventory Table:
inv_id /* Unique ID number for this row */
fruit_name /* Same as before */
fruit_cost /* Price id from the Price Table */
quantity /* Quantity on hand */
This would give you a unique link between the two tables based on the
price_id, and permit you to run simple SQL statements to count your fruit,
find out what you have in stock and at what cost, etc. Note that this
would permit you to have, say, two cases of apples @ 5.00 and another two
@ 5.20; each would have it's own cost row. You would decrease quantity
available as shipments go out, removing the row when quantity becomes
zero (assuming you need no further record of it). Likewise, you can tell
when the rows in the Price Table are no longer needed by checking for
matches in the Inventory Table.fruit_cost row.
Good luck.
David Coburn ...{uunet,pyramid}!infmx!coburn
-------------------------------
--
- Scott