Archives of the TeradataForum

Message Posted: Tue, 05 Sep 2006 @ 13:50:35 GMT


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

Subj:		Re: How to merge contiguous date ranges

From:		Dieter Noeth

Anomy.Anom wrote:

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.

...

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

If they overlap it's getting a bit more complicated :-)

I usually use nested OLAP functions for that type of question. One possible solution:

     SELECT
        thekey,
        thevalue,
        MIN(start_date) AS start_date,
        NULLIF(MAX(end_date), DATE '9999-12-31') AS end_date
     FROM
       (
        SELECT
          thekey,
          thevalue,
          start_date,
          end_date,
          prev_end_date,
          SUM(CASE WHEN start_date - prev_end_date <= 1 THEN 0 ELSE 1 END)
            OVER (PARTITION BY thekey, thevalue
                  ORDER BY start_date
                  ROWS UNBOUNDED PRECEDING) AS grp
        FROM
         (
          SELECT
            thekey,
            thevalue,
            start_date,
            COALESCE(end_date, DATE '9999-12-31') AS end_date,
            MIN(end_date)
              OVER (PARTITION BY thekey, thevalue
                    ORDER BY start_date
                    ROWS 1 PRECEDING) AS prev_end_date
          FROM anomy
         ) dt
       ) dt
     GROUP BY
        thekey,
        thevalue,
        grp
     ORDER BY
        thekey,
        start_date

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

My mom always told me:

Don't take gifts from unknown strangers...

Dieter


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

Attachments

Library

Quick Reference