Re: How to select the <= date row?

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 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 <= _at_AsOfDate
and EffectiveTo > _at_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