CODEWITHSUNDEEP
sqlteradata
Jacob Ian
Jacob Ian

Reputation: 699

SQL: make small spans into 1 large continuous span

I have some data where after a period of time a type of data can renew.

         ID       Type         Start           End
         1         A            01/01/2017     02/01/2017
         1         A            02/01/2017     03/01/2017
         1         A            03/01/2017     05/01/2017
         1         A            04/01/2017     06/01/2017
         1         A            09/01/2017     10/01/2017

I currently do the following

   SELECT 
       A.ID,
       A.Type,
       B.Start,
       A.End
   FROM my_data as A
   INNER JOIN my_data as B
       ON A.ID = B.ID
       AND A.start>B.start
       AND A.start < B.end
   WHERE A.type = B.Type
    AND A.end-B.start <=365;

So that I get a data set where if an ID has the same type and the start/end spans have NO gaps between them (but they can overlap) the first start is start and the last end is end (and they don't go beyond the year aka the earliest start is already 01/01/2017 but some ends can go into 2018 we want to stop them on the last day of the year).

Currently we get a data set like this:

     ID       Type        Start            End    
      1        A           01/01/2017      03/01/2017
      1        A            02/01/2017      05/01/2017
      1        A            04/01/2017      06/01/2017

And I just keep repeating the similar logic on the new table. But it's taking so many times in real life it feels like there should be an easier solution.

The table I want is like so:

        ID          Type     Start           End
         1           A       01/01/2017      06/01/2017 
         1           A       09/01/2017      10/01/2017

Upvotes: 0

Views: 91

Answers (1)

dnoeth
dnoeth

Reputation: 60513

Teradata provides a nice extension to Standard SQL to normalize overlapping ranges:

SELECT 
   ID
  ,Type
  -- split the period back into seperate dates
  ,Begin(pd) AS Start
  ,End(pd) AS End
FROM
 (
   SELECT NORMALIZE 
      ID
     ,Type
     -- NORMALIZE only works with periods, so create it on the fly
     ,PERIOD(Start, End) AS pd
   FROM my_data 
 ) AS dt

Upvotes: 1

Related Questions