Detect Consecutive Dates Ranges Using SQL

Detect consecutive dates ranges using SQL

No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

WITH t AS (
  SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i
  FROM @d
  GROUP BY InfoDate
)
SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)

How can I identify groups of consecutive dates in SQL?

You can do this with a clever application of window functions. Consider the following:

select name, date, row_number() over (partition by name order by date)
from t

This adds a row number, which in your example would simply be 1, 2, 3, 4, 5. Now, take the difference from the date, and you have a constant value for the group.

select name, date,
       dateadd(d, - row_number() over (partition by name order by date), date) as val
from t

Finally, you want the number of groups in sequence. I would also add a group identifier (for instance, to distinguish between the last two).

select name, date,
       count(*) over (partition by name, val) as NumInSeq,
       dense_rank() over (partition by name order by val) as SeqID
from (select name, date,
             dateadd(d, - row_number() over (partition by name order by date), date) as val
      from t
     ) t

Somehow, I missed the part about weekdays and holidays. This solution does not solve that problem.

Find non consecutive date ranges

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

SQL Counting Consecutive Days in Date Ranges

This is a gaps-and-islands problem. One option is to use lag() and a window sum() to build groups of adjacent records. You can then aggregate by group and count the number of consecutive days, and finally filter on the greatest streak by name:

select name, max(consecutive_days) consecutive_days
from (
    select name, datediff(day, min(start_date), max(end_date)) + 1 consecutive_days
    from (
        select t.*, 
            sum(case when start_date = dateadd(day, 1, lag_end_date) then 0 else 1 end) over(partition by name order by start_date) grp
        from (
            select t.*, 
                lag(end_date) over(partition by name order by start_date) lag_end_date
            from mytable t
        ) t
    ) t
    group by name, grp
) t
group by name

Demo on DB Fiddle:

name   | consecutive_days
:----- | ---------------:
Jenny  |                3
Johnny |                9

Find All Consecutive Dates in SQL

You can do this using window functions. You can identify groups of consecutive dates by subtracting row_number() from the date. With the group, you can then count the number of rows, and choose only those that have 3 or more rows:

select code, food, date
from (select t.*, count(*) over (partition by code, food, grp) as cnt
      from (select t.*,
                   dateadd(day, - row_number() over (partition by code, food order by date), date) as grp
            from #temptable t
           ) t
     ) t
where cnt >= 3;

merge consecutive dates ranges using SQL

This is a Gaps & Islands problem. You can use the typical solution using LAG(). For example:

select
  max(client_id) as client_id,
  max(status) as status,
  min(start_date) as start_date,
  max(end_date) as end_date
from (
  select *, sum(i) over(partition by client_id order by start_date) as g
  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
) y
group by client_id, g
order by client_id, g

Result:

 client_id  status  start_date  end_date   
 ---------- ------- ----------- ---------- 
 1          1       2022-01-01  2022-01-04 
 1          1       2022-01-12  2022-01-15 
 2          1       2022-01-03  2022-01-03 
 2          1       2022-01-05  2022-01-06 

See running example at db<>fiddle.

Find the people who are login 3 consecutive dates

Based on the specific sample data provided, you could use analytic min and max to get the first and last date for each name, count the difference in days and the number of logins which must be 3 with 2 days between first and last date.

You haven't specific a RDBMS so the date functions may need amending as appropriate, however all RDBMS support the same functionality.

select date, name
from (
    select *, 
      DateDiff(day,Min(date) over(partition by name),  
      Max(date) over(partition by name))diff, 
      Count(*) over(partition by name) qty
    from t
)t
where diff=2 and qty=3
order by date;

Count consecutive dates in SQL as one instance

This was taken from Sean Lange comment. His link (HERE) was spot on to what you need. The final code from the link was...

WITH
cteGroupedDates AS
( --=== Find the unique dates and assign them to a group.
 -- The group looks like a date but the date means nothing except that adjacent
 -- dates will be a part of the same group.
SELECT UniqueDate = SomeDate,
    DateGroup  = DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY SomeDate), SomeDate)
FROM #MyHead
GROUP BY SomeDate
)
--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the
 -- Start and End dates of each group of contiguous daes.  While we're at it,
 -- we can also figure out how many days are in each range of days.
SELECT StartDate = MIN(UniqueDate),
    EndDate   = MAX(UniqueDate),
    Days      = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1
FROM cteGroupedDates
GROUP BY DateGroup
ORDER BY StartDate
;

Making some name changes to try and make it easier to understand...

WITH
dateGroup AS
( --This is used to distinguish the different continuous sets of dates    
SELECT UniqueDate = date,
    DateGroup  = DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY date), date)
FROM userdate
GROUP BY date
)

--Using dateGroup to get the groups of dates we can utilize it to get the count for them
SELECT StartDate = MIN(UniqueDate),
    EndDate   = MAX(UniqueDate),
    Days      = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,
    u.user_id
FROM dateGroup JOIN userdate u ON u.date = UniqueDate
GROUP BY DateGroup, u.user_id
ORDER BY StartDate
;

I added JOIN userdate u ON u.date = UniqueDate after the FROM dateCount to get the user ID. Also added u.user_id to the group by. Wouldn't work because u.user_id is in the SELECT (need information in the SELECT in the GROUP BY).

Data From Table:

Proof:

----------EDIT 1----------

I am going to take a guess at what you truly want!

This is what I came up. Two different queries both with the same results.

First query:

WITH
dateGroup AS
( --This is used to distinguish the different continuous sets of dates    
SELECT UniqueDate = date,
DateGroup  = DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY date), date),
user_id
FROM userdate
GROUP BY date, user_id
),
userIDGroup AS
( --This is used to get the previous table that you saw in my original answer
SELECT COUNT(d.user_id) as 'Instances',
d.user_id
FROM dateGroup d
GROUP BY DateGroup, d.user_id
)

SELECT
COUNT(u.user_id) AS 'Instances',
u.user_id
FROM userIDGroup u
GROUP BY u.user_id
;

Second Query (the one that I also prefer):

WITH
dateGroup AS
( --This is used to distinguish the different continuous sets of dates    
SELECT UniqueDate = date,
DateGroup  = DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY date), date),
user_id
FROM userdate
GROUP BY date, user_id
)

SELECT count(c.user_id) AS 'Instances', c.user_id
FROM
(
SELECT COUNT(d.user_id) as 'Instances',
d.user_id
FROM dateGroup d
GROUP BY DateGroup, d.user_id
) c GROUP BY c.user_id
;

Proof:

---

Related Topics