Archives of the TeradataForum

Message Posted: Fri, 03 Jan 2003 @ 15:08:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Does anyone deal with data that has a multiple date/time dimensionality?

As an insurance company, we have to deal with date/time logic in two dimensions. We need to know when we knew when the attributes to a key changed as well as the effective and expiration dates. For example, a policyholder might call on 1/30 to let us know his son received his drivers license on 1/1 and he was trading in his car next week on 2/5 when it gets delivered. Although this is a very simple example we need to know that as of 1/30 we knew the policy changed but we also need to know that what we thought was in effect up until now actually was no longer in effect between 1/1 and 1/30, something new is in effect between1/1 and 2/5 and then will change again on 2/5.

We currently handle this by pairing the transaction and Revision dates for a row and also pairing the effective and expiration dates for that row. Selecting a row involves finding the row for a specific date using this pseudo code logic:

      Where TransactionDate  <= DATE <  RevisionDate
      And   EffectiveDate <= DATE < ExpirationDate

For the most part, this works but it has its share of limitations.

Any suggestions of principles or concepts to try or reference materials to check?

Thanks

Jim

	<Prev	Next>		<<First	<Prev	Next>	Last>>