How to convert list of dates to list of date ranges grouped by a condition

Given a data set in the below format:

State   AuditDate
1       2016-11-01
1       2016-11-02
1       2016-11-03
0       2016-11-04
1       2016-11-05
0       2016-11-06
0       2016-11-07
1       2016-11-08
1       2016-11-09
1       2016-11-10
0       2016-11-11
0       2016-11-12

Is there an easy way to convert it to a list of date ranges grouped (and ordered) by the state? (with the last record ending on GETDATE()) E.g.

State   StartDate   EndDate
1       2016-11-01  2016-11-04
0       2016-11-04  2016-11-05
1       2016-11-05  2016-11-06
0       2016-11-06  2016-11-08
1       2016-11-08  2016-11-11
0       2016-11-11  2016-12-05

I initially tried doing this using Row_Number and Dense_Rank but couldn't get it to work, eventually I did come up with a solution (see below) however it feels very clunky and inefficient, there must be a better way to do this?

```
IF OBJECT_ID('tempdb..#Records') IS NOT NULL DROP TABLE #Records
Create Table #Records
(
State bit
,AuditDate date
,StartDate date
,EndDate date
)

Insert Into #Records
(
State
,AuditDate
)
Select 1, '2016-11-01'
union Select 1, '2016-11-02'
union Select 1, '2016-11-03'
union Select 0, '2016-11-04'
union Select 1, '2016-11-05'
union Select 0, '2016-11-06'
union Select 0, '2016-11-07'
union Select 1, '2016-11-08'
union Select 1, '2016-11-09'
union Select 1, '2016-11-10'
union Select 0, '2016-11-11'
union Select 0, '2016-11-12'

Update r1
Set StartDate = SP.StartOfPeriod
From
#Records r1
Outer Apply
(
Select
Max(AuditDate) as LastTimeDifferent
From
#Records r2
Where
r2.AuditDate LD.LastTimeDifferent or LD.LastTimeDifferent is null)
and r3.State = r1.State
) as SP

Update r1
Set EndDate = isnull(ND.NextTimeDifferent, GETDATE())
From
#Records r1
Outer Apply
(
Select
Min(AuditDate) as NextTimeDifferent
From
#Records r2

With help of this other question, you can try :

WITH 
  t AS
    ( SELECT state, auditDate, x = CASE WHEN state = LAG(state) OVER (ORDER BY auditDate) 
                           THEN NULL ELSE 1 
                       END
      FROM #Records
    )
select min(auditDate), dateadd(dd, 1, max(auditDate)), state
from (
    SELECT state, auditDate, c = COUNT(x) OVER (ORDER BY auditDate) 
    FROM t 
    ) t
group by c, state
ORDER BY 1 ;

That assumes that records are every day. If not, let's do some small changes:

WITH 
    t AS
    ( SELECT state, auditDate, x = CASE WHEN state = LAG(state) OVER (ORDER BY auditDate) 
                           THEN NULL ELSE 1 
                       END
      FROM #Records
    ),
    t2 as
    (
    SELECT state, auditDate, c = COUNT(x) OVER (ORDER BY auditDate) 
    FROM t 
    )

select a.state, min(a.auditDate) as startDate, isnull(min(b.auditDate), getdate()) as endDate
from t2 a
    left join t2 b on a.c = b.c - 1
group by a.state, a.c
order by 2

StackExchange Database Administrators Q#157191, answer score: 2