Sql query - how to get when a row first got a certain value

I have a table with Rows like this:

   ID  StatusId   Date
    1    1        2001-01-01
    2    1        2001-01-02
    3    2        2001-01-03
    4    3        2001-01-04
    5    1        2001-01-05
    6    2        2001-01-06
    7    2        2001-01-07
    8    1        2001-01-08
    9    1        2001-01-09

I need to get the date when the current value of the status was originally changed. For the above example, the last value is 1, and it's changed in row 8, so the result would be 2001-01-08.

How would you do this?

If you need a table to test with, here it is:

DECLARE @Tbl AS TABLE (ID INT, StatusId INT, Date DATETIME)
INSERT INTO @Tbl(ID, StatusId, Date)
SELECT 1,1,'2001-01-01' UNION
SELECT 2,1,'2001-01-02' UNION
SELECT 3,2,'2001-01-03' UNION
SELECT 4,3,'2001-01-04' UNION
SELECT 5,1,'2001-01-05' UNION
SELECT 6,2,'2001-01-06' UNION
SELECT 7,2,'2001-01-07' UNION
SELECT 8,1,'2001-01-08' UNION
SELECT 9,1,'2001-01-09' 

    SELECT * FROM @Tbl

Answers




As a single query

This query requires IDs without gaps and it will get you the last record after a status change and it will also work when there's just one record in the table or multiple of them with the same status (isnullprovides the required functionality)

select top 1 Datefrom @tbl t1
    left join @tbl t2
    on (t2.ID = t1.ID - 1)
where (isnull(t2.StatusID, -1) <> t1.StatusID)
order by ID desc

Last where clause changes a null value (when there's no upper record) to -1. If you do have a status with this value, you should change this number to some non-existing status value.