SQL Count Consecutive Days

Count consecutive days in SQL

You can subtract a incrementing value from the dates to get a constant when the dates are incrementing by one. The rest is just aggregation.

Because you have not clearly specified a database, I'll use standard SQL syntax:

select name, count(*), min(date), max(date)
from (select t.*,
             row_number() over (partition by name order by date) as seqnum
      from t
     ) t
group by name, date - seqnum * interval '1 day'
order by name, min(date);

This should work in Postgres (which is close to Standard SQL). In SQL Server, it would be:

group by name, dateadd(day, -seqnum, date)

SQL count consecutive days

This is a Gaps and Islands problem. The easiest way to solve this is using ROW_NUMBER() to identify the gaps in the sequence:

SELECT  UserName,
        UserDate,
        UserCode,
        GroupingSet = DATEADD(DAY, 
                            -ROW_NUMBER() OVER(PARTITION BY UserName 
                                                        ORDER BY UserDate), 
                            UserDate)
FROM    UserTable;

This gives:

UserName    | UserDate      | UserCode   | GroupingSet
------------+---------------+------------+-------------
user1       | 09-01-2014    | 1          | 08-31-2014    
user1       | 09-02-2014    | 0          | 08-31-2014    
user1       | 09-03-2014    | 1          | 08-31-2014    
user1       | 09-08-2014    | 1          | 09-04-2014    
user1       | 09-09-2014    | 0          | 09-04-2014    
user1       | 09-10-2014    | 1          | 09-04-2014    
user1       | 09-11-2014    | 1          | 09-04-2014    
user2       | 09-01-2014    | 1          | 08-31-2014    
user2       | 09-04-2014    | 1          | 09-02-2014    
user2       | 09-05-2014    | 1          | 09-02-2014    
user2       | 09-06-2014    | 0          | 09-02-2014    
user2       | 09-07-2014    | 1          | 09-02-2014    

As you can see this gives a constant value in GroupingSet for consecutive rows. You can then group by this colum to get the summary you want:

WITH CTE AS
(   SELECT  UserName,
            UserDate,
            UserCode,
            GroupingSet = DATEADD(DAY, 
                                -ROW_NUMBER() OVER(PARTITION BY UserName 
                                                            ORDER BY UserDate), 
                                UserDate)
    FROM    UserTable
)
SELECT  UserName,
        StartDate = MIN(UserDate),
        EndDate = MAX(UserDate),
        Result = COUNT(NULLIF(UserCode, 0))
FROM    CTE
GROUP BY UserName, GroupingSet
HAVING COUNT(NULLIF(UserCode, 0)) > 1
ORDER BY UserName, StartDate;

Example on SQL Fiddle

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

Counting Consecutive days SQL Server

You can get the periods of absences using:

select name, min(date), max(date), count(*) as numdays, type
from (select a.*,
             row_number() over (partition by name, type order by date) as seqnum_ct
      from absence a
     ) a
group by name, type, dateadd(day, -seqnum_ct, date);

Here is a SQL Fiddle for this.

You can add having count(*) > 1 to get periods with one day or more. This seems useful. I don't understand what the ultimate output is. The description just doesn't make sense to me.

If you want the number of absences that are 2 or more days, then use this as a subquery/CTE:

select name, count(*), type
from (select name, min(date) as mindate, max(date) as maxdate, count(*) as numdays, type
      from (select a.*,
                   row_number() over (partition by name, type order by date) as seqnum_ct
            from absence a
           ) a
      group by name, type, dateadd(day, -seqnum_ct, date)
     ) b
where numdays > 1
group by name, type;

Calculate Number of Consecutive Days Where a Condition Applies Across Two Columns

Note that any gaps between dates will be treated as consecutive days.

with data as (
    select id, date,
        case when min(balance) >= 0 then 0 else 1 end as tally,
        sum(case when min(balance) >= 0 then 1 else 0 end)
            over (partition by id order by date) as grp
    from t
    group by id, date
)
select id, date,
    sum(tally) over (partition by id, grp, tally order by date) as running_days
from data
order by id, date;

To treat missing dates as nonconsecutive try:

    sum(case when min(balance) >= 0 then 1 else 0 end)
        over (partition by id order by date) +
    datediff(day, min(date) over (partition by id), date) -
    row_number() over (partition by id order by date) + 1 as grp

https://rextester.com/NKBZG48737

SQL count longest consecutive days with dates table

You can do this as a gap-and-islands problem. Assuming one login per day (as in the example data), you can subtract an enumerated sequence, and the value is constant on consecutive days.

In Standard SQL, the logic for all sequences look like:

select userid, min(login_date), max(login_date)
from (select t.*,
             row_number() over (partition by userid order by login_date) as seqn
      from t
     ) t
group by userid, login_date - seqnum * interval '1 day';

In Postgres, you can actually get the longest using distinct on:

select distinct on (userid) userid, min(login_date), max(login_date)
from (select t.*,
             row_number() over (partition by userid order by login_date) as seqn
      from t
     ) t
group by userid, login_date - seqnum * interval '1 day'
order by userid, count(*) desc;

Note: If thee are duplicates for user/day combinations, use dense_rank() instead of row_number().

If you want to handle weekends and holidays, then a calendar table is recommended.

SQL - Counting consecutive days with gaps and conditions

I might have found a solution with a different approach.
I'll need to tidy up the code some, but at first pass it seems to produce the desired outcome.

;WITH CTE AS (SELECT LOWER(k1.Agent) AS Agent, CAST(k1.Date AS DATE) AS Date, k1.Category
FROM Hours k1
WHERE Category = 'SickLeave'
AND Date >= CONVERT(DATETIME, '01.01.2021 00:00:00', 104))

, CTE2 AS (SELECT LOWER(k2.Agent) AS Agent, CAST(k2.Date AS DATE) AS Date, k2.Category
FROM Hours k2
WHERE Date >= CONVERT(DATETIME, '01.01.2021 00:00:00', 104))

, CTE3 AS (SELECT LOWER(b.Agent) AS Agent, b.Date, (CASE WHEN b.Date = a.Date THEN 1 ELSE 0 END) AS SickLeaveFlg
FROM CTE2 b
LEFT OUTER JOIN CTE a ON b.Date = a.Date AND b.Agent = a.Agent)

, CTE4 AS (SELECT LOWER(Agent) AS Agent, Date, SickLeaveFlg
FROM CTE3
GROUP BY Agent, Date, SickLeaveFlg)

, CTE5 AS (SELECT LOWER(Agent) AS Agent, Date, SickLeaveFlg,
ROW_NUMBER() OVER (PARTITION BY Agent, (CASE WHEN SickLeaveFlg LIKE 1 THEN 'Y' ELSE 'N' END) ORDER BY Date) AS DateSeq,
ROW_NUMBER() OVER (PARTITION BY Agent ORDER BY Date) AS AgentSeq
FROM CTE4)

SELECT  LOWER(Agent) AS Agent,
        Date,
        SickLeaveFlg,
        ROW_NUMBER() OVER (PARTITION BY Agent, (CASE WHEN SickLeaveFlg LIKE 1 THEN 'Y' ELSE 'N' END) ORDER BY Date) AS DateSeq,
        (CASE WHEN SickLeaveFlg = 1 THEN ROW_NUMBER() OVER (PARTITION BY Agent, SickLeaveFlg, AgentSeq - DateSeq ORDER BY Date) ELSE 0 END) AS ResultSeq
FROM CTE5
ORDER BY Date

---

Related Topics