Re: How to select the <= date row?

In article <4pi4lj$hbq_at_spot.Xpressnet.com>, slamotte_at_jenex.mb.ca (Steven Lamotte) writes:
>stefanbm_at_iafrica.com (Stefan Mahs) wrote:
>
>>Hi there, I would appreciate it if you could give me some ideas on the
>>following problem:

 .....>
>>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.

I ran into the same problem. I joined the 2 tables and ran my reports against the 3rd table, at a time when no more data-entry was being done. If you are just doing an occasional query maybe you could just query the 2 table and not join them? I noticed that performance can vary greatly depending on how the indexes are set up. With tables as big as yours, I don't know if this would ever run fast enough but here it is... I ran this in a vms .com file in batch.

create table aud_join as
select x.rescode, x.startdate, x.enddate, x.pgmcode, x.hospcode,   x.pgylevel, h.startdate h_startdate, h.enddate h_enddate,   h.hospcode h_hospcode
from system.resstat95 x, system.reshosp95 h   where (x.startdate, x.rescode) in
  (select max(startdate), rescode
   from system.resstat95 y
where (y.rescode = h.rescode and
 y.startdate <= h.startdate)
 group by y.rescode);

• Peggy -