Example 1N,1,1,3,1,1,4,11---->2---->3->Example 2N,1,1,3,1,1,1,11---->2------->ExampleN,3,3,1,1,1,6,11>2>3------>4->
SELECT COUNT(*) + 1 FROM #temp1 WHERE DataDifference > 1
;With tblDifference as(Select Row_Number() OVER (Order by DateRecorded) as RowNumber,Name, DateRecorded from #m_TestTable)Select Cur.Name,Cur.DateRecorded as CurrentDay,Prv.DateRecorded as PreviousDay,Datediff(Day, Prv.DateRecorded, Cur.DateRecorded) as DateDifference,Cur.RowNumber,0 Grpinto #temp1FromtblDifference Cur Left Outer Join tblDifference PrvOn Cur.RowNumber = Prv.RowNumber + 1 Order by Cur.DateRecordedALTER TABLE #temp1 ALTER COLUMN RowNumber int NOT NULLALTER TABLE #temp1 ADD CONSTRAINT tmp_pk PRIMARY KEY (RowNumber)DECLARE @i AS INT = 1UPDATE #temp1SET Grp = @i, @i = @i + isnull(cast(cast(DateDifference - 1 as bit) as integer),0)SELECT * FROM #temp1
WITH SampleData AS (SELECT Name, RecordedDateFROM (Values ('John', dateadd(day,1,GetDate())),('John', dateadd(day,2,GetDate())),('John', dateadd(day,3,GetDate())),('John', dateadd(day,6,GetDate())),('John', dateadd(day,7,GetDate())),('John', dateadd(day,8,GetDate())), ('John', dateadd(day,12,GetDate())),('John', dateadd(day,13,GetDate())))a (Name, RecordedDate))SELECT Name, COUNT(*)FROM ( SELECT Name FROM ( SELECT Name, RecordedDate ,rn=RecordedDate-ROW_NUMBER() OVER (PARTITION BY Name ORDER BY RecordedDate) FROM SampleData) a GROUP BY Name, rn) bGROUP BY Name