Sql-server – How to convert list of dates to list of date ranges grouped by a condition

gaps-and-islandssql servert-sql

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 < r1.AuditDate 
            and r2.State != r1.State
    ) as LD
    Outer Apply
    (
        Select
            Min(AuditDate) as StartOfPeriod
        From
            #Records r3
        Where 
            (r3.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
        Where 
            r2.AuditDate > r1.AuditDate 
            and r2.State != r1.State
    ) as ND


Select Distinct
     State
    ,StartDate
    ,EndDate
From
    #Records
Order By
    StartDate

Best Answer

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
Related Question