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