alexa

Counting consecutive days with gaps and conditions in SQL ?

Counting consecutive days with gaps and conditions in SQL ?

We can use dateadd and - row_number to group the periods of Sick Leave. Following your comment I've added a second query to find the total number of days Sick Leave where more than one day taken together ie not counting single days.

 with distinctHours as(
select distinct * from Hours),
cte as(
select
  Agent, 
  Date, 
  Category,
  DATEADD(DD,
  -1 * row_number() over (partition by agent,Category order by date)
  ,Date)as continuous
from distinctHours)
select
  Agent,
  min(date) start_date,
  count(date) number_days,
  Category
from cte
where Category = 'SickLeave'
group by 
  Agent,
  Category,
  continuous
order by min(date)
GO 
 Agent  | start_date              | number_days | Category 
:----- | :---------------------- | ----------: | :--------
Agent1 | 2022-02-25 00:00:00.000 |           3 | SickLeave
Agent1 | 2022-03-02 00:00:00.000 |           1 | SickLeave
Agent1 | 2022-03-15 00:00:00.000 |           1 | SickLeave
Agent1 | 2022-03-17 00:00:00.000 |           4 | SickLeave
Agent1 | 2022-04-13 00:00:00.000 |           3 | SickLeave
Agent1 | 2022-04-18 00:00:00.000 |           5 | SickLeave
Agent1 | 2022-04-25 00:00:00.000 |           3 | SickLeave
Agent1 | 2022-04-29 00:00:00.000 |           2 | SickLeave 
 with distinctHours as(
select distinct * from Hours),
cte as(
select
  Agent, 
  Date, 
  Category,
  DATEADD(DD,
  -1 * row_number() over (partition by agent,Category order by date)
  ,Date)as continuous
from distinctHours),
preQuery as(
select
  Agent,
  min(date) start_date,
  count(date) number_days,
  Category
from cte
where Category = 'SickLeave'
group by 
  Agent,
  Category,
  continuous
)
select 
  Agent,
  sum(number_days) continuous_sick_leave,
  Category
from preQuery
where number_days > 1
group by Agent, Category;
GO 
 Agent  | continuous_sick_leave | Category 
:----- | --------------------: | :--------
Agent1 |                    20 | SickLeave 

db<>fiddle


280 0
7

Write a Comments


* Be the first to Make Comment

GoodFirms Badge
GoodFirms Badge

Fix Your Meeting With Our SEO Consultants in India To Grow Your Business Online

Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry