SQL Server - conditional counter increment

Question

What I want to do is create grouped sequences for continuous date ranges. Take the following data:

Person|BeginDate |EndDate
A     |1/1/2015  |1/31/2015
A     |2/1/2015  |2/28/2015
A     |4/1/2015  |4/30/2015
A     |5/1/2015  |5/31/2015
B     |1/1/2015  |1/30/2015
B     |8/1/2015  |8/30/2015
B     |9/1/2015  |9/30/2015

If BeginDate in the current row> 1 day from EndDate in the previous row, then increase the counter by 1, otherwise assign the value of the current counter. The sequence will look like this:

Person|BeginDate |EndDate  |Sequence
A     |1/1/2015  |1/31/2015|1
A     |2/1/2015  |2/28/2015|1
A     |4/1/2015  |4/30/2015|2
A     |5/1/2015  |5/31/2015|2
B     |1/1/2015  |1/30/2015|1
B     |8/1/2015  |8/30/2015|2
B     |9/1/2015  |9/30/2015|2

Divided and reset for each person.

For testing:

 CREATE TABLE ##SequencingTest(
 Person char(1)
,BeginDate date
,EndDate date)

INSERT INTO ##SequencingTest
VALUES
('A','1/1/2015','1/31/2015')
,('A','2/1/2015','2/28/2015')
,('A','4/1/2015','4/30/2015')
,('A','5/1/2015','5/31/2015')
,('B','1/1/2015','1/30/2015')
,('B','8/15/2015','8/31/2015')
,('B','9/1/2015','9/30/2015')

Answer 1

You can do this with lag()and then the cumulative sum:

select t.*,
       sum(flag) over (partition by person order by begindate) as sequence
from (select t.*,
             (case when datediff(day, lag(endDate) over (partition by person order by begindate), begindate) < 2
                   then 0
                   else 1
              end) as flag
      from t
     ) t;

Answer 2

If continuous end dates are always 1 day before the next start date, you can do something really primitive:

SELECT S1.Person, S1.BeginDate, S1.EndDate, SUM(S2.Cntr) AS Sequence
FROM Sequencing S1
INNER JOIN (SELECT Person, BeginDate,
      CASE WHEN EXISTS (SELECT Person FROM Sequencing S2 WHERE S2.[EndDate] =
            DATEADD(d, -1, S1.[BeginDate]) AND S2.Person = S1.Person) THEN 0 ELSE 1 END AS Cntr
      FROM [Sequencing] S1
     ) S2
  ON S1.Person = S2.Person
  AND S1.BeginDate >= S2.BeginDate
GROUP BY S1.Person, S1.BeginDate, S1.EndDate
ORDER BY S1.Person, S1.BeginDate, S1.EndDate

. , "1/31/2015" "8/31/2015" .

, @GordonLinoff, , . SQL Server .