i want to find if some of all the consecutive date ranges has gap between. Some of the dates are not consecutive, in this case it will return the RowId of the single range.

Table Name: Subscriptions

i want a sql statement to find RowId of non consecutive ranges for each client and status in (1,3) (example of result)

I want to solve the problem using SQL only. thanks

CodePudding user response：

One way you could do this is to use Lag (or lead) to identify gaps in neighbouring rows' date ranges and take the top N rows where the gap exceeds 1 day.

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;

CodePudding user response：

You can detect gaps with LAG() and mark them. Then, it's easy to filter out the rows. For example:

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

Or simpler...

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