Hi

The third example you gave doesn't appear to conform with the rules use on the other examples. I would have expect 4 based on the previous examples

Example 1

N,1,1,3,1,1,4,1

1---->2---->3->

Example 2

N,1,1,3,1,1,1,1

1---->2------->

Example

N,3,3,1,1,1,6,1

1>2>3------>4->

If my assumption is correct then you could do a count of differences greater than one and add one

SELECT COUNT(*) + 1 FROM #temp1 WHERE DataDifference > 1

If you want to assign a group then you could do something like the following quirky update. I may a some changes to your original query into #temp1 to accommodate it.

;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 Grp

into #temp1

From

tblDifference Cur Left Outer Join tblDifference Prv

On Cur.RowNumber = Prv.RowNumber + 1

Order by Cur.DateRecorded

ALTER TABLE #temp1 ALTER COLUMN RowNumber int NOT NULL

ALTER TABLE #temp1 ADD CONSTRAINT tmp_pk PRIMARY KEY (RowNumber)

DECLARE @i AS INT = 1

UPDATE #temp1

SET Grp = @i,

@i = @i + isnull(cast(cast(DateDifference - 1 as bit) as integer),0)

SELECT * FROM #temp1

Hope this helps