All Forums Analytics
CuriaG 2 posts Joined 08/11
01 Sep 2011
group data by ranges

Hi

I'm new to this but i need to make this:

I have the following values

    id    val    date_from    date_to
1    1    0    1/4/2011    1/4/2011
2    1    0    1/1/2011    1/1/2011
3    1    0    1/5/2011    1/9/2011
4    1    0    12/31/2010    12/31/2010
5    1    0    1/2/2011    1/2/2011
6    1    1    1/3/2011    1/3/2011
7    2    3    1/6/2011    1/10/2011
8    2    3    1/1/2011    1/1/2011
9    3    4    1/1/2011    1/1/2011
 

I should obtain

id    val    date_from    date_to

1     0       12/31/2010   1/2/2011

1     0       1/4/2011       1/9/2011

2     3      1/1/2011        1/1/2011

2     3       1/6/2011    1/10/2011

1    1    1/3/2011    1/3/2011

....

 

So i need to group the data with the same id, val where the dates overlap and obtain the min from date_from and max from date_to withn the overlapped group. sorry if i didn't explain very well.

 

Thank you

 

 

Programming is 10% science, 20% ingenuity, and 70% getting the ingenuity to work with the science.
ulrich 816 posts Joined 09/09
15 Nov 2011

check http://forums.teradata.com/forum/analytics/date-gap-analysis-0

I think its the same problem

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

joquinn238 4 posts Joined 02/12
16 Mar 2012

I read the post on "date gap analysis" but I have no clue how to begin to write it. I am new to Teradata and would really appreciate some help on writing the query that will consolidate consecutive and overlapping date spans into one span....Need help very bad.

 

Thank you so much

 

John

You must sign in to leave a comment.