Hello,

I have a data set as -

ID       Set Date          DB Date

100     Null                 07/01/15

100     07/05/15         07/02/15

100     07/10/15        07/08/15

I want to able to get 2 unique dates

1. 07/02/15 - As I want the DB date for ID - 100 when set date changed from a null value to non null value.

2. 07/08/15 - As I want the DB date for ID - 100 when a non null set date changes.

The table has such records for lot of different ID's , so if anyone could tell me a efficient way to calculate it would be great .

Thanks,

the question can not be answered as it is. Efficiency is a matter of specific database structure and data. if you have two solutions A and B, and solution A gives you ten times better solution in one database, it is not saying that B will not be 100 times better on another database! For example, one simple index can make all different, using different data type can make it behave differently, The SQL Server version can make it execute in different way using different execution plan, and so on...

Please post DDL&#

Create table test (ID int, SetDate  date,  DBDate date)
Insert into test values(100,null,'07/01/2015'),(100,'07/05/2015','07/02/2015'),(100,'07/10/2015','07/08/2015')

;with mycte as (
Select ID, SetDate,DBDate, lag(SetDate) Over(Partition by ID Order by SetDate) lagSetDate  
from test)

Select DBDate from mycte
Where SetDate is not null  

drop table test

Thanks Jingyang. I forgot to mention I would need the query in teradata SQL. As there is no lead / lag in teradata. DO you think lag can be replicated using teradata ? 

Thanks a lot.