Q:

How to aggregate "continuous" records in sql server?

I have a table like this: 
name        date        record
A           20180725    1
A           20180725    2
B           20180721    5
B           20180721    7
B           20180721    8
B           20180721    9

and I hope to aggregate "continuous" records, expected result like: 
name        date        r1    r2
A           20180725    1     2
B           20180721    5     5
B           20180721    7     9

where r1 and r2 are the start and end of a group of continuous records. 
How to achieve this in sql server in a good way? 

A:

This problem is a gaps-and-islands problem.  It has a pretty simple solution in your case.  If you subtract a sequence from record, then the value is constant when the record values are a sequence.
So:
select name, date, min(record), max(record)
from (select name, date, record,
             row_number() over (partition by name, date order by record) as seqnum
      from t
     ) t
group by name, date, (record - seqnum)

