Need to select Date range from Table A which does not fall under Date range of Table B

Analytics

Need to select Date range from Table A which does not fall under Date range of Table B

Hello, Im new to Teradata and i need some help in following scenario

Table A

PK    Start Date      End Date

100   01-01-2005  30-06-2006

100   01-01-2011  31-01-2011 ---- This record need to be selected

100   01-02-2011  31-07-2013

100   01-08-2011  31-12-8888

Table B

PK    Start Date      End Date

100  01-09-2000    30-06-2006

100  01-02-2011    31-12-8888

The record which has 1st jan 2011 (start date) to 31st jan 2011 (end date) from Table A does not fall under the date range in Table B. Please help me to find way to select this invalid record from Table A using Table B.

1 REPLY
Junior Contributor

Re: Need to select Date range from Table A which does not fall under Date range of Table B

Hi Mohan,

try this

SELECT * FROM tableA AS A
WHERE NOT EXISTS
(
SELECT * FROM tableB AS B
WHERE A.PK=B.PK
AND (B.Start_Date, B.End_Date) OVERLAPS (A.Start_Date, a.End_Date)
)

I don't know if this reurns what you need, because you didn't tell about your rules for matching: 

e.g. what should be rerurned if the first row is 

100   01-01-2005  31-07-2006?

Dieter