Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 16 Jan 2003 @ 05:30:56 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: History table monthend logic
From:   Jim Downey


We use a very similar structure. I posted a note last week asking if anyone uses dual date chains such as you describe.

We would use something like Where plan_start_date <= DATE and DATE < plan_end_date To select an ASOf row. You cannot use BETWEEN because you have two rows with inclusive dates. In your example, if the date selected is 2003-01-14, both rows are returned.

We use a calendar to select the as of date then join to the calendar.

We implemented on an earlier version of Teradata that did not support year=9999 so we used 3500-01-01 as our high value date. We had some data that had bad dates that we made 3500-12-31 so it was outside the normal range of dates and would not be returned until corrected.

We use 0001-01-01 as a low value date. There are some cases where we want a row returned no matter what so we date chain between 0001- 01-01 and 3500-01-01.


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020