如何將日期列表轉換為按條件分組的日期範圍列表

給定以下格式的數據集：

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

有沒有一種簡單的方法可以將其轉換為按州分組（和排序）的日期範圍列表？（最後一條記錄在 GETDATE() 結束）例如

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

我最初嘗試使用 Row_Number 和 Dense_Rank 執行此操作，但無法使其正常工作，最終我確實想出了一個解決方案（見下文），但感覺非常笨拙且效率低下，必須有更好的方法來做到這一點？

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

在這個其他問題的幫助下，您可以嘗試：

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 ;

假設每天都有記錄。如果沒有，讓我們做一些小改動：

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

引用自：https://dba.stackexchange.com/questions/157191