Find last updated record where other column has a specific value and status

Question

I have the following:

Corp_ID   ExpireDate  RecordUpdateDate       Status    State
100       2013/08/02  2013-04-02  00:00:00   Active     CO
100       2013/08/05  2013-08-02  00:00:00   Active     CO
100       2013/08/26  2013-08-05  00:00:00   Closed     NV
100       2013/10/24  2013-08-26  00:00:00   Active     CO   <<<------
100       2013/11/14  2013-10-24  00:00:00   Active     NV
100       12/31/9999  2013-11-14  00:00:00   Active     AZ

I am trying to get the record that corresponds to the expire date 2013/10/24. My goal is to show the moment the current status (active - bottom row) started. The correct return would be 2013/10/24 as that is when the corporation was updated from a status 'closed' to a status 'active'.

The answer is (I think)

select top 1 Y.* from
  (
  select RecordUpdateDate, Status, ExpireDate,
    dense_rank() over (order by RecordUpdateDate desc) as rank
  from [dbo].[Table_1]
  ) X
inner join
  (
  select RecordUpdateDate, Status, ExpireDate,
    dense_rank() over (order by RecordUpdateDate desc) as rank
  from [dbo].[Table_1]
  ) Y
on X.rank = Y.rank- 1  where X.Status <> Y.Status  

Your answers were key!

Answer 1

You can use this query. It joins 2 identical subqueries where I used dense_rank() to get the order of record updates and then I get the record where the status changed in the join condition:

select Y.* from
  (
  select RecordUpdateDate, Status, ExpireDate,
    dense_rank() over (order by RecordUpdateDate) as rank
  from table1
  ) X
inner join
  (
  select RecordUpdateDate, Status, ExpireDate,
    dense_rank() over (order by RecordUpdateDate) as rank
  from table1
  ) Y
on X.rank = Y.rank - 1 and X.Status = 'Closed' and Y.Status = 'Active'

SQL Fiddle Demo