Filtering records that have overlapping dates

Database
Enthusiast

Filtering records that have overlapping dates

Hi,

     I need to filter only those records in a partition that have overlapping dates. Consider the following scenario,

 MBR_KEY START_DATE END_DATE

1 123 1/1/2013 9/8/2013

2 123 9/1/2013 9/8/2013

3 123 9/9/2013 10/8/2013

4 245 1/1/2013 9/8/2013

5 245 9/1/2013 9/8/2013

6 245 9/9/2013 10/8/2013

Here I need to select only the row numbers 1,2 and 4,5. And then select only one record from each pair based on START_DATE.

I tried the following,

SEL * FROM VOL_SAMPLE_1 WHERE TYPE_CD in (10)

QUALIFY

ABS(MIN(END_DATE) OVER (PARTITION BY MBR_KEY ORDER BY START_DATE ROWS 1 PRECEDING)

-

MAX(START_DATE) OVER (PARTITION BY MBR_KEY ORDER BY START_DATE ROWS 1 PRECEDING)

)<>1

Is there a better way of doing this?

7 REPLIES
KVB
Enthusiast

Re: Filtering records that have overlapping dates

IDW_Corporate_Service_SSR_101_b

CT T3

(

ID INTEGER,

STARTDATE DATE,

ENDDATE DATE

)

INS INTO T3 VALUES(123,'2013-01-01','2013-08-09')

INS INTO T3 VALUES(123,'2013-01-09','2013-08-09')

INS INTO T3 VALUES(123,'2013-09-09','2013-08-10')

SELECT

  B.ID

, B.STARTDATE

, B.ENDDATE

FROM   T3  AS B

JOIN   T3  AS A

    ON   B.ID =  A.ID

        AND B.STARTDATE        <  A.STARTDATE

    AND B.ENDDATE       >= A.STARTDATE

UNION

SELECT

  A.ID

, A.STARTDATE

, A.ENDDATE

FROM   T3  AS B

JOIN   T3  AS A

    ON   B.ID =  A.ID

        AND B.STARTDATE        <  A.STARTDATE

    AND B.ENDDATE       >= A.STARTDATE

ORDER BY 1,2,3

Enthusiast

Re: Filtering records that have overlapping dates

Hi bikky6, thanks. That is pretty straight forward. But the tables I am working with are so huge and two self-joins maybe time consuming. And I want to pick up only one of the two rows with overlapping dates, based on some more criteria. I came up with this approach which works,

SEL * FROM T3

QUALIFY ( ENDDATE >= MIN(STARTDATE) OVER (PARTITION BY ID ORDER BY ID ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) )

Enthusiast

Re: Filtering records that have overlapping dates

Hi,

Why dont you use overlap function for Dates?

Khurram
Enthusiast

Re: Filtering records that have overlapping dates

If I use the overlap function, I need two pointers on adjacent rows. Again it will ask for a self-join. Please correct me if I am wrong.
Enthusiast

Re: Filtering records that have overlapping dates

Hi all, I want to group the records based on some key TYPE_CD and want to see only those paritions which have distinct DATES in the partition. A parition may have more than 2 records hence the below query will not work..

SEL CLNDR_END_DT,TYPE_CD,CUST_KEY

FROM TABLE_1 WHERE CLNDR__END_DT='2014-01-31'

GROUP BY 1,2,3 HAVING COUNT(*)>2 AND

MIN(TRMNTN_DT)<CLNDR_END_DT AND

MAX(TRMNTN_DT)>CLNDR_END_DT AND

MIN(EFCTV_DT)<>MAX(EFCTV_DT) AND

MIN(TRMNTN_DT)<>MAX(TRMNTN_DT)

Any help?

Teradata Employee

Re: Filtering records that have overlapping dates

What exactly you mean by 'partitions'?

Also, can you give a sample data and expected result?

Enthusiast

Re: Filtering records that have overlapping dates

I would strongly suggest you investigate the Period datatype and it's associated functions.  Depending upon which version of TD you're running, you may not have to alter the table DDL, as 14.10 allows a derived period in views. 

Any period overlaps, intersections, or sequences can be evaluated with much simpler SQL if the Period datatype is used.  And it can be done without implementing any of the temporal features of Teradata.