All Forums Database
atomhouse 7 posts Joined 10/13
18 Oct 2013
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?

KVB 124 posts Joined 09/12
22 Oct 2013

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

atomhouse 7 posts Joined 10/13
28 Oct 2013

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) )

M.Saeed Khurram 544 posts Joined 09/12
28 Oct 2013

Hi,
Why dont you use overlap function for Dates?
 

Khurram

atomhouse 7 posts Joined 10/13
28 Oct 2013

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.

atomhouse 7 posts Joined 10/13
24 Feb 2014

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?

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

What exactly you mean by 'partitions'?
 
Also, can you give a sample data and expected result?

-- If you are stuck at something .... consider it an opportunity to think anew.

VandeBergB 182 posts Joined 09/06
24 Feb 2014

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.

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.