How to find the row difference in Teradata

Database
Enthusiast

How to find the row difference in Teradata

Hi All,

I have come across a typical problem. Basically to aggregate the broken insurance details based on their continuity. Let me tell you the details with an example

Insurance# Start Date End Date

123456 01-01-1999 01-01-2000

123456 01-01-2000 01-01-2001

123456 01-01-2001 01-01-2002

123456 01-01-2002 01-01-2003

123456 01-01-2004 01-01-2005

123456 01-01-2005 01-01-2006

123456 01-01-2007 01-01-2008

123456 01-01-2008 01-01-2009

Now you can see the insurance no 123456 was discontinued in twice. When I query my results should be

123456 01-01-1999 01-01-2003

123456 01-01-2004 01-01-2006

123456 01-01-2007 01-01-2009

All I have one table with 160 million of such records. Your help is highly appreciated. Also note that cursors are ruled out as my client does not allow me to use cursors.
2 REPLIES
Enthusiast

Re: How to find the row difference in Teradata

The following will work:

Select D1.InsNo, D1.StartDate,D2.EndDate
From

-- Get All The Start Dates

(Select InsNo, StartDate
From Insurances
Where (InsNo, StartDate)
NOT IN (Select InsNo, EndDate
From Insurances) ) D1

Inner Join
-- Get All The End Dates

(Select InsNo, EndDate
From Insurances
Where (InsNo, EndDate)
Not In (Select InsNo, StartDate
From Insurances) ) D2

-- Although it says Inner, it is actually a Cross Join for every InsNo!
On D1.InsNo = D2.InsNo

Where EndDate > StartDate
Qualify Rank() Over (Partition By D1.InsNo, D1.StartDate
Order By D2.EndDate ) = 1

Order By 1,2
;

(It returns Insurance numbers with a single start/ end date - not sure whether you want these or not!)

It should work OK if the table has a NUPI of Insurance#. If you use a UPI of Insurance#/ Start Date or Insurance#/ End Date, you many need to see your local Teradata Salesman for a few more nodes (not just for this query!)
Enthusiast

Re: How to find the row difference in Teradata

Hey Jim,

Thanks My friend, after little tweaking it works perfectly. You made my day. I appreciate your help!!!!