Archives of the TeradataForum

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

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

Bruce,

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.

Jim

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