Count how many times a Boolean value changes in SQL Server

advertisements

I have this table:

     [SDate - DateTime]                 [Value] - Represents a boolean
    2010-11-16 10:10:00                    1
    2010-11-16 10:11:00                    0
    2010-11-16 10:12:00                    1
    2010-11-16 10:13:00                    1

I need a query to count how many times the value changes.

For example:

    2010-11-16 10:10:00                    0
    2010-11-16 10:11:00                    1
    2010-11-16 10:12:00                    0
    2010-11-16 10:13:00                    0
    2010-11-16 10:14:00                    1
    2010-11-16 10:15:00                    0
    2010-11-16 10:16:00                    1
    ...
                                       5 changes


    2010-11-16 10:10:00                    0
    2010-11-16 10:11:00                    0
    2010-11-16 10:12:00                    0
    2010-11-16 10:13:00                    1
    2010-11-16 10:14:00                    1
    2010-11-16 10:15:00                    1
    2010-11-16 10:16:00                    1
    ...
                                        1 change


You can do this with lag():

select count(*)
from (select t.*, lag(value) order by (sdate) as prev_value
      from table t
     ) t
where prev_value <> value ;