Archives of the TeradataForum

Message Posted: Tue, 05 Sep 2006 @ 09:58:23 GMT

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

<-- Anonymously Posted: Monday, September 04, 2006 23:21 -->

I have historical data in a table with start and end dates. There are some records where there are contiguous date ranges (ie the start date of one record is end date + 1 of the previous record) for the same data values - I would like a query to merge these records. Does anyone know how to go about this? For example, assuming I have the following data:

     thekey   thevalue   start_date   end_date
     A        1          2006-01-01   2006-01-05
     A        2          2006-01-06   2006-01-10
     A        2          2006-01-11   2006-01-15
     A        2          2006-01-16   2006-01-20
     A        1          2006-01-21   2006-01-25
     A        1          2006-01-26   2006-01-30
     A        2          2006-01-31   2006-02-05
     A        2          2006-02-06   2006-02-07
     A        1          2006-02-08   2006-02-10
     A        3          2006-02-11   2006-02-20
     A        3          2006-02-21

I would like to get the following result set

     A        1          2006-01-01   2006-01-05
     A        2          2006-01-06   2006-01-15
     A        2          2006-01-17   2006-01-20
     A        1          2006-01-21   2006-01-30
     A        2          2006-01-31   2006-02-07
     A        1          2006-02-08   2006-02-10
     A        3          2006-02-11

Date ranges per key never overlap, but there may be gaps.

First correct answer wins a poke in the eye with a blunt stick.

Thanks.

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