Menu
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
897
T-SQL doesn't provide a built-in function to count the number of times a particular string appears within another string, so to find out how many times a word appears in a row, you have to build your own count function.
There sure is! As you've already learned, COUNT(*) will count all the rows in the table, including NULL values. On the other hand, COUNT(column name) will count all the rows in the specified column while excluding NULL values.
With COUNT(1), there is a misconception that it counts records from the first column. What COUNT(1) really does is that it replaces all the records you get from query result with the value 1 and then counts the rows meaning it even replaces a NULL with 1 meaning it takes NULLs into consideration while counting.
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 ;
This should work in earlier versions too..
;WITH cte
AS (SELECT Row_number()OVER(ORDER BY sdates) rn,*
FROM <tablename>)
SELECT Sum(CASE WHEN a.boolvalue = b.boolvalue THEN 0 ELSE 1 END)
FROM cte a
JOIN cte b
ON a.rn = b.rn + 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
