Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   How to merge contiguous date ranges
 
From:   Anomy Anom

<-- 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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016