Hi there, I would appreciate it if you could give me some ideas on the
following problem:
I have a couple of tables related to each other and they store time
variant data. The "main table" contains event info. All the general
details of any event that effects the value of a policy are logged here.
Specific info about each event is logged in a set of event-specific
tables. The result of this is that not all the tables get a new row for
every event that is logged in the "main table".
I need to get the complete status of a policy, or set of policies, as it
was at any particular point in time. This means supplying at least a date
at which the policy's value should be retrieved, say 1 Jan 1995. Normaly
one would just join all the tables policy number, but in this case there
may not be row for the specified date in one or more of the related event
specific tables. In this case I need to get the row for the closesed
previous date. The row with the largest date less than or equal to the
specified date, is the one that was still in force at the specified date.
Two things make this a particularly challenging problem:
- This query has to be done from a query tool, so we are restricted to a
single SQL statement (unless Business Objects can build a report from
more than one SQL statement).
- Selecting the max(specified_date) where event_date <= specified_date
works fine, but it will take a couple of days to wade through 40
million rows.
e.g.:
Main_Event_Tbl Event_A_Tbl Event_B_Tbl
-------------- ----------- -----------
95/01/01 95/01/01
95/02/01 95/02/01
95/03/01 95/03/01
