Re: How to select the <= date row?

stefanbm@iafrica.com (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 Would it help to have each policy have a unique identifier (identity column?) and store an EffectiveFrom and EffectiveTo date with each record? I do this for all history-bearing columns. The PK would be the PolicyId/EffectiveTo combo: PolicyId int identity ... Status char(1) EffectiveFrom datetime EffectiveTo datetime To get the record in effect as of a given datetime, use the following terms in your where clause: ... and EffectiveFrom <= @AsOfDate and EffectiveTo > @AsOfDate --- The first time you write a new policy, set the EffectiveFrom = getdate() and EffectiveTo = '12-31-9999'. Updates to a policy first set the current record's EffectiveTo = getdate(), and insert a new record with the same Id, EffectivrFrom = getdate(), EffectiveTo = '12-31-9999'. To delete a policy, set its Status from 'A'ctive to 'I'nactive and write a new record. That way, "as of" some prior date, the record is still active, but after that it's deleted. If you're doing something totally different then forgive my rambling. Steve