All Forums Database
lbzh 3 posts Joined 05/12
15 May 2012
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.

indrajit_td 50 posts Joined 10/09
15 May 2012

TRY:

 

sel

Ordernum,

Name,

zip,

min(Start_Date),

max(End_Date)

from <your table>

group by 1,2,3 ;

lbzh 3 posts Joined 05/12
15 May 2012

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?

ulrich 816 posts Joined 09/09
16 May 2012

on which database version are you?

can gaps exists in the history?

start and end overlap - which is included where?

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

lbzh 3 posts Joined 05/12
16 May 2012

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.

ulrich 816 posts Joined 09/09
16 May 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

abc_munmun 1 post Joined 10/12
04 Oct 2012

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.

dnoeth 4628 posts Joined 11/04
05 Oct 2012

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
 

Dieter

You must sign in to leave a comment.