Consolidate date ranges using Teradata

Question

I need to take a list of date ranges and consolidate them. In our existing table, sometimes the FROM_DT and TO_DT are the same and the FROM_DT in the following row is 1 day after the previous row's TO_DT. I want to make it just 1 row with the FROM_DT of row 1 and the TO_DT of row 2.

CREATE TABLE ML_ORIGINAL_RANGES
(MEMBER_ID VARCHAR(3),
FROM_DT DATE FORMAT 'yyyy-mm-dd',
TO_DT DATE FORMAT 'yyyy-mm-dd');

INSERT INTO ML_ORIGINAL_RANGES ('001', '2023-08-11', '2023-08-11');
INSERT INTO ML_ORIGINAL_RANGES ('001', '2023-08-12', '2023-08-12');
INSERT INTO ML_ORIGINAL_RANGES ('001', '2023-08-13', '2023-08-15');
INSERT INTO ML_ORIGINAL_RANGES ('001', '2023-08-19', '2023-08-19');
INSERT INTO ML_ORIGINAL_RANGES ('001', '2023-08-20', '2023-08-23');
INSERT INTO ML_ORIGINAL_RANGES ('002', '2023-07-14', '2023-07-14');
INSERT INTO ML_ORIGINAL_RANGES ('002', '2023-07-14', '2023-07-17');

SELECT * FROM ML_ORIGINAL_RANGES ORDER BY MEMBER_ID, FROM_DT, TO_DT;

With that, I want:

Range1

to consolidate to:

Range2

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Jul 11 at 9:48

dnoeth · Accepted Answer · 2025-07-09 16:28:44Z

1

This is a simple task for Teradata's NORMALIZE option:

SELECT * FROM ML_ORIGINAL_RANGES ORDER BY MEMBER_ID, FROM_DT, TO_DT;
with cte as
 ( -- works on PERIODs only
   select NORMALIZE -- combine overlapping periods
      MEMBER_ID
       -- periods are Inclusive-exclusive [ [, but your data is inclusive-inclusive [ ]
       -- NEXT is convering it to [ [
     ,period(FROM_DT, NEXT(TO_DT)) as pd
   from ML_ORIGINAL_RANGES
 )
-- split period back into start/end (LAST reverts the NEXT)
select MEMBER_ID, begin(pd) as START_DATE, LAST(pd) as END_DATE
from cte

answered Jul 9 at 16:28

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michelle Lehman Jul 9 at 16:55

Thank you! Matches the results of an excel macro perfectly!

Kargathara Aakash · Accepted Answer · 2025-07-09 15:18:59Z

0

You can use ROW_NUMBER along with a date difference trick to group and consolidate the date ranges in Teradata. Here's the working solution:

-- Step 1: Create Sample Data
CREATE VOLATILE TABLE ML_ORIGINAL_RANGES
(
    MEMBER_ID VARCHAR(3),
    FROM_DT DATE,
    TO_DT DATE
) PRIMARY INDEX (MEMBER_ID, FROM_DT)
ON COMMIT PRESERVE ROWS;

INSERT INTO ML_ORIGINAL_RANGES VALUES ('001', DATE '2023-08-11', DATE '2023-08-11');
INSERT INTO ML_ORIGINAL_RANGES VALUES ('001', DATE '2023-08-12', DATE '2023-08-12');
INSERT INTO ML_ORIGINAL_RANGES VALUES ('001', DATE '2023-08-13', DATE '2023-08-15');
INSERT INTO ML_ORIGINAL_RANGES VALUES ('001', DATE '2023-08-19', DATE '2023-08-19');
INSERT INTO ML_ORIGINAL_RANGES VALUES ('001', DATE '2023-08-20', DATE '2023-08-23');
INSERT INTO ML_ORIGINAL_RANGES VALUES ('002', DATE '2023-07-14', DATE '2023-07-14');
INSERT INTO ML_ORIGINAL_RANGES VALUES ('002', DATE '2023-07-14', DATE '2023-07-17');

-- Step 2: Consolidate Date Ranges
WITH OrderedRanges AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY MEMBER_ID ORDER BY FROM_DT) AS rn
    FROM ML_ORIGINAL_RANGES
),
GroupMarkers AS (
    SELECT 
        MEMBER_ID, 
        FROM_DT, 
        TO_DT, 
        rn,
        FROM_DT - rn AS grp
    FROM OrderedRanges
),
Consolidated AS (
    SELECT 
        MEMBER_ID, 
        MIN(FROM_DT) AS FROM_DT, 
        MAX(TO_DT) AS TO_DT
    FROM GroupMarkers
    GROUP BY MEMBER_ID, grp
)
SELECT *
FROM Consolidated
ORDER BY MEMBER_ID, FROM_DT;

This merges continuous or overlapping date ranges exactly like your example.

edited Jul 9 at 15:18

answered Jul 9 at 14:20

Kargathara Aakash

463 bronze badges

5 Comments

Michelle Lehman Jul 9 at 15:00

@Kargathara_Aakas - I'm sorry, I jumped the gun. MEMBER_ID 002, should result in 7/14/2023 - 7/17/2023. It worked for the 1st member but not the 2nd.

Kargathara Aakash Jul 9 at 15:19

Thank you for the clarification! I’ve updated the solution to ensure it works for both MEMBER_ID 001 and MEMBER_ID 002, including cases where the same start date is used with different end dates. This version should handle all such scenarios correctly.

Michelle Lehman Jul 9 at 16:02

@Kargathara_Aakash - Same results. MEMEBER_ID 002 still had 2 rows. I added some more rows to insert for the example table.

Michelle Lehman Jul 9 at 16:58

@Kargathara_Aakash - Thank you for your suggestions and working on this!

|

Collectives™ on Stack Overflow

Consolidate date ranges using Teradata

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related