Time depended data
Date: 1996/02/20
Message-ID: <Dn3JLo.KvE_at_solair1.inter.NL.net>#1/1
Hi,
I am sure some of you had the same problem...
We are developing a system in which allmost every table is time-depended. For example an employer has a start_date (hiring) and a end_date (resigning).
Almost every table has a start_date and an end_date. This means that in every query we have to include conditions in the WHEN-clause to only select data that was valid on the this date. We do not only query with sysdate but also want to get the situation for let's say a year ago.
The biggest problems comes when we want to summerize data over a period. For example we have some tables:
EMPLOYEE (employer)
JOB (his job)
SICK (he/she's been sick)
EMPLOYEE --- JOB --- SICK
1 n 1 n
We want to get the percentage of SICK for one employee. You can get quite complex situations like this:
EMPLOYEE |-----------------------------------------|
JOB <--------| |-----------| |-----| |------>
|---------------------------|
SICK |--| |-----| |-----------|
You can understand that everything can change on every day. I do not want to execute a query for each day (this would be quite slow with 365 days and 5000 employees).
Does anyone have experience with this kind of date/time-depended data? I'm just wondering why there isn't something like Oracle Time Server, which is Oracle Server that is completely build for Time-depended data. This way there would be some kind oo extension of SQL to make it support this date/time-depended data.
If you have any comments, feel free to reply.
Bye,
Wilfred
The Netherlands
E-mail : W.van.der.Deijl_at_inter.nl.net Homepage: http://www.inter.nl.net/users/W.van.der.Deijl
(including the MicroProse Grand Prix II FAQ!) Received on Tue Feb 20 1996 - 00:00:00 CET