4

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
3

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
1

First cte is creating the grouping information/field GRP The 2nd cte is for startDate and endDate with min, max and the LEAD is to get the correct upper limit

;WITH GRP AS
(
    SELECT 
    *               
    ,ROW_NUMBER() OVER(ORDER BY AuditDate ASC)
        -ROW_NUMBER() OVER(PARTITION BY State ORDEr BY AuditDate ASC) AS Grp
FROM
    #Records AS R
)
, MinMax AS
(
    SELECT
        State
        ,MIN(AuditDate) AS StartDate
        ,MAX(AuditDate) AS EndDate
    FROM
        GRP
    GROUP BY
        State
        ,Grp
)

SELECT
    State
    ,StartDate  
    ,LEAD(StartDate,1,GETDATE()) OVER(ORDER BY StartDate) AS EndDate
FROM
    MinMax
ORDER BY
    StartDate
    ,EndDate

Output

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.