SQL Question

Database
Fan

SQL Question

I have some data in a table and looks like the Input table below.

I would like to write a Teradata SQL to inspect the sequential records represented in the Input.



If the key which is composed of Ordernum, Name and Zip is duplicated, then keep one version of the key record with the earliest Start Date from the first record and the latest Start Date from the last record in the group. To eliminate the dupe, the dupe records must be in succession.



If a record is not duped simply write it out to the output.





Input:

Ordernum Name Zip Start_Date End_Date

1 John 90210 3/15/2011 3/16/2011

1 John 90210 3/16/2011 3/18/2011

1 John 42538 3/18/2011 4/3/2011

1 John 90210 4/3/2011 4/9/2011

2 Jerry 23245 4/12/2011 4/15/2011

2 Paul 23245 4/15/2011 4/22/2011

2 Paul 23245 4/22/2011 4/29/2011



Output:

Ordernum Name Zip Start_Date End_Date

1 John 90210 3/15/2011 3/18/2011

1 John 42538 3/18/2011 4/3/2011

1 John 90210 4/3/2011 4/9/2011

2 Jerry 23245 4/12/2011 4/15/2011

2 Paul 23245 4/15/2011 4/29/2011



I know you would probably need an analytical function, but I am not sure how to begin with this.

7 REPLIES
Enthusiast

Re: SQL Question

TRY:

sel

Ordernum,

Name,

zip,

min(Start_Date),

max(End_Date)

from <your table>

group by 1,2,3 ;

Fan

Re: SQL Question

Hi,

Thanks for the attempt.

The problem with the above solution is that for lines 2 and 4 on the input, they would be deduped. As per the requirement the dupe records must be listed in succession. In the case of the 2nd and 4th record they are not in succession yet the 2nd record will be eliminated with that solution. Any other ideas?

Senior Supporter

Re: SQL Question

on which database version are you?

can gaps exists in the history?

start and end overlap - which is included where?

Fan

Re: SQL Question

I am on version 13.1.

There should be no gaps in history, so as soon as one end date completes there should be the same date for the start date of the next record.

Senior Supporter

Re: SQL Question

check dieters solution in

http://forums.teradata.com/forum/general/need-help-merging-consecutive-and-overlapping-date-spans

You would need to convert the dates to a period etc.

Re: SQL Question

i need to pull a string between two chars.for ex- abc#read#efg  .i need to puul the string between the 1st and 2nd '#'.Substring won't help.so i am not sure how to proceed.

Junior Contributor

Re: SQL Question

Of course Substring will help, ugly, but working:

CASE
WHEN x LIKE '%#%#%'
THEN SUBSTRING(SUBSTRING(x FROM POSITION('#' IN x) + 1)
FROM 1
FOR POSITION('#' IN SUBSTRING(x FROM POSITION('#' IN x) + 1)) - 1)
END

Dieter