versioning based on date for a set of records

General

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. 

1 REPLY
Enthusiast

Re: versioning based on date for a set of records

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.