How can i get a gap from a history table for repeating groups over time

I have a historical table with data like as bellow :

SK ID STATUS EFF_DT EXP_DT 
1 486007909 APP 7/22/2009  8/22/2009
2 486007909 APP 8/22/2009  10/01/2009
3 486007909 CAN 10/01/2009 11/01/2009
4 486007909 CAN 11/02/2009 12/12/2009
5 486007909 APP 12/12/2009 NULL

The EXP_DT is null mean that the row is active.

I want to return a group of data each time Status changes

The expected result like as bellow :

SK ID STATUS EFF_DT EXP_DT                 GAP
1 486007909 APP 7/22/2009  8/22/2009        1
2 486007909 APP 8/22/2009  10/01/2009       1
3 486007909 CAN 10/01/2009 11/01/2009       2
4 486007909 CAN 11/02/2009 12/12/2009       2
5 486007909 APP 12/12/2009 NULL             3

Thanks for help !

Answer

This is classic gaps-and-islands problem.

We can solve it by using LAG to check for differences, then a windowed COUNT to get the GAP number.

You may want to add a partitioning clause, such as PARTITION BY ID

SELECT *,
  GAP = COUNT(IsDiff) OVER (ORDER BY EFF_DT ROWS UNBOUNDED PRECEDING)
FROM (
    SELECT *,
      IsDiff = CASE WHEN LAG(STATUS, 1, '') OVER (ORDER BY EFF_DT) <> STATUS THEN 1 END
    FROM YourTable t
) t