Archives of the TeradataForum
Message Posted: Fri, 03 Jan 2003 @ 15:08:10 GMT
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|