Re: How to select the <= date row?

If the date field is indexed, it seems to me that there is enough information in a " max(specified_date) where event_date <= specified_date" statement to tell the server how to quickly locate the value. Even if it does a scan, it will scan the index because only the indexed field is involved.

Stefan Mahs wrote:
>
> 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:
>
> 1) 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).
>
> 2) 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
Received on Thu Jun 06 1996 - 00:00:00 CEST