查找非连续日期范围

Question

我想知道是否有一些连续的日期范围之间有间隔。有些日期不是连续的，在这种情况下，它将返回单个范围的RowId。

表名:Subscriptions

<表类> RowId ClientId 状态 StartDate可以 EndDate tbody><<tr>11101/01/202202/01/202221103/01/202204/01/202231112/01/202215/01/202242103/01/202206/01/2022

一种方法是使用Lag(或lead)来识别相邻行日期范围中的差距，并取差距超过1天的前N行。

select top (1) with ties rowId 
from t
where status in (1,3)
order by 
case when DateDiff(day, lag(enddate,1,enddate) 
over(partition by clientid order by startdate), startdate) >1 
then 0 else 1 end;

您可以检测与LAG()的差距并标记它们。然后，很容易过滤掉这些行。例如:

select *
from (
select *,
case when dateadd(day, -1, start_date) >
lag(end_date) over(partition by client_id order by start_date) 
then 1 else 0 end as i
from t
) x
where i = 1

或简单…

select *
from (
select *,
lag(end_date) over(partition by client_id order by start_date) as prev_end
from t
) x
where dateadd(day, -1, start_date) > prev_end