SQL - Identify Continuous groups of data

Question

For a table like below, the need is to identify continuous occurrence of a certain value in Column D. I have a C# script which performs this, but would be great if there's a query (or two-three, based on temp tables) that could accomplish this.

Column C : Can have only 0 or 1

SELECT   Column A,
         Column B,
         Column C
FROM     Table
ORDER BY Column A,
         Column B

Sample Table Data:

Column A,Column B,Column C
Employee 1,Value 1,0
Employee 1,Value 2,0
Employee 1,Value 3,0
Employee 1,Value 4,1
Employee 1,Value 5,1
Employee 1,Value 6,1
Employee 1,Value 7,0
Employee 1,Value 8,0
Employee 1,Value 9,1
Employee 1,Value 10,1
Employee 1,Value 11,1
Employee 1,Value 12,1

Expected result (EDIT: Made minor change in Column C):

Column A, Start Value, End Value, Column C
Employee 1, Value 1, Value 1, 0
Employee 1, Value 2, Value 2, 0
Employee 1, Value 3, Value 3, 0
Employee 1, Value 4, Value 6, 3
Employee 1, Value 7, Value 7, 0
Employee 1, Value 8, Value 8, 0
Employee 1, Value 9, Value 12, 4

Answer 1

You can do this with the difference in row numbers. Run the subquery to see how this works:

select a, c, min(b), max(b)
from (select t.*,
             row_number() over (partition by a order by b) as seqnum_a,
             row_number() over (partition by a, c order by b) as seqnum_ac
      from t
     ) t
group by a, c, (seqnum_a - seqnum_ac);