SQL Server - counting the number of attribute changes in a dataset (not at the same time)

Question

I have a query that returns either 1 or 0, based on whether the event occurred on a given date or not. This is sorted by date. Basically, a simple set of results:

Date | Type --------------------- 2010-09-27 1 2010-10-11 1 2010-11-29 0 2010-12-06 0 2010-12-13 1 2010-12-15 0 2010-12-17 0 2011-01-03 1 2011-01-04 0 

What I would like to do now is to count the number of separate, non-matching instances of "0 is," that is, count the number of different groups of 0.

In the above example, the answer should be 3 (1 group of 2, then another group of 2, then finally 1 to finish).

Hopefully the above example illustrates what I'm trying to get to. I have been looking for some time, but it is difficult for me to briefly describe what I am looking for, and therefore have not found anything suitable.

Thanks in advance,

Josh

Answer 1

This is a variant of the "islands" problem. My first answer uses the Itzik Ben Gan row_number double trick to efficiently define continuous data groups. The combination of Type,Grp identifies each individual island in the data.

You can learn more about different approaches to solving this problem here.

 ;WITH T AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY Date) - ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Date) AS Grp FROM YourTable ) SELECT COUNT(DISTINCT Grp) FROM T WHERE Type=0 

My second answer requires one pass through the data. It is not guaranteed to work, but on the same principle as the technique that many people successfully use to concatenate strings without problems.

 DECLARE @Count int = 0 SELECT @Count = CASE WHEN Type = 0 AND @Count <=0 THEN -@Count +1 WHEN Type = 1 AND @Count > 0 THEN - @Count ELSE @Count END FROM YourTable ORDER BY Date SELECT ABS(@Count) 

Answer 2

You can give each line a number in the CTE. Then you can join the table to find the previous row. Knowing the previous line, you can sum the number of times when the previous line is 1 and the current line is 0. For example:

 ; with NumberedRows as ( select row_number() over (order by date) as rn , type from YourTable ) select sum(case when cur.type = 0 and IsNull(prev.type,1) = 1 then 1 end) from NumberedRows cur left join NumberedRows prev on cur.rn = prev.rn + 1 

Answer 3

Take a look at this example using Sql Server 2005+

 DECLARE @Table TABLE( Date DATETIME, Type INT ) INSERT INTO @Table SELECT '2010-09-27',1 INSERT INTO @Table SELECT '2010-10-11',1 INSERT INTO @Table SELECT '2010-11-29',0 INSERT INTO @Table SELECT '2010-12-06',0 INSERT INTO @Table SELECT '2010-12-13',1 INSERT INTO @Table SELECT '2010-12-15',0 INSERT INTO @Table SELECT '2010-12-17',0 INSERT INTO @Table SELECT '2011-01-03',1 INSERT INTO @Table SELECT '2011-01-04',0 ;WITH Vals AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY Date) ROWID FROM @Table ) SELECT v.* FROM Vals v LEFT JOIN Vals vNext ON v.ROWID + 1 = vNext.ROWID WHERE v.Type = 0 AND (vNext.Type = 1 OR vNext.Type IS NULL)