Search code examples
sqlsybase

How to look for changes in a table that keeps historical data?


Example:

FIELD_1 | DATE
X | 2010-01-01
X | 2010-02-01
X | 2010-03-01
Y | 2010-04-01
Y | 2010-05-01
Y | 2010-05-01

I would like the SELECT to return the following: (a change occurred in FIELD_1) Y | 2010-04-01

Is there an easy way to do this?


Solution

  • Writen for mssql:

    declare @t table(field varchar(20), date date)
    
    insert @t values('X', '2010-01-01')
    insert @t values('X', '2010-01-02')
    insert @t values('X', '2010-01-03')
    insert @t values('Y', '2010-01-04')
    insert @t values('Y', '2010-01-05')
    insert @t values('Y', '2010-01-06')
    
    
    select b.* from @t a join @t b on dateadd(day, 1, a.date) = b.date
    and a.field <> b.field
    

    Result:

    field                date
    -------------------- ----------
    Y                    2010-01-04
    

    It should also work for sybase if you replace @t with your table and ignore the test data