All Forums General
amaze17 2 posts Joined 01/14
07 Jan 2014
versioning based on date for a set of records

Hi,

 

I am new to Teradata and I am working on query for which I greatly appreciate any help.

 

i have data in a table as shown below:

 

date

id

value

1-Jan-13

1

100

2-Jan-13

1

100

3-Jan-13

1

100

4-Jan-13

1

200

5-Jan-13

1

200

6-Jan-13

1

100

7-Jan-13

1

100

 

im trying to group the records based on the id and val and version the records with startdate and end date .

 

Desired output:

 

start date

end date

id

value

1-Jan-13

3-Jan-13

1

100

4-Jan-13

5-Jan-13

1

200

6-Jan-13

7-Jan-13

1

100

 

Tried to use partition by and min and max functions but couldn't get far. 

 

chill3che 99 posts Joined 10/12
09 Jan 2014

Hi Amaze,
I think this might help you.  May be some may give you even more easy solution..)


sel id, val, st,max(est) from (
sel id, val, min(cold) over(partition by id order by cold reset when val<> min(val) over(partition by id order by id, cold rows between 1 preceding and 1 preceding)) as st,
max(cold) over(partition by id order by cold reset when val<> max(val) over(partition by id order by id, cold rows between 1 preceding and 1 preceding)) as est
from abc) xy
group by id, val, st

Please test it thoroughly as I am not sure for all cases.

Thanks,
Cheeli

You must sign in to leave a comment.