Date gaps and islands

Transact-SQL
1

I have sql server 2008

Data is like this

declare @t table (holiday_date date)
insert into @t (holiday_date)
values ('2020-02-05')
,('2020-03-23')
,('2020-05-01')
,('2020-05-22')
,('2020-05-23')
,('2020-05-25')
,('2020-05-26')
,('2020-05-27')
,('2020-07-31')
,('2020-08-01')
,('2020-08-03')
,('2020-08-14')
,('2020-08-29')
,('2020-10-30')
,('2020-12-25')

Expected result

Date From Date To
2020-02-05 2020-02-05
2020-03-23 2020-03-23
2020-05-01 2020-05-01
2020-05-22 2020-05-23
2020-05-25 2020-05-27
2020-07-31 2020-07-31
2020-08-01 2020-08-01
2020-08-03 2020-08-03
2020-08-14 2020-08-14
2020-08-29 2020-08-29
2020-10-30 2020-10-30
2020-12-25 2020-12-25

2

Its ok

I found the solution from net

;with ct as
(
SELECT
     holiday_date
    ,grp = DATEADD(DAY ,-1 * DENSE_RANK() OVER (ORDER BY holiday_date) ,holiday_date)
FROM @t
)
select [date_from]= min(holiday_date)
, [date_to]= max(holiday_date)
from ct
group by grp
3

The question now is do you know how and why that works?

1 Like
4

First i wondered. Then looked deep.

A brilliant mathematics.

Since the day difference between two consecutive date is always 1,
-1 multiplied by day difference will create group for consecutive and non consecutive dates.
Then it is simply min and max.on group.

In my case dense_rank, rank and row_number all works.

So nice of you to ask?

Love U!!!

5

Perfect and excellent . Thanks for the feedback. Just in case you want to show anyone else, here's a short article on the subject.
https://www.sqlservercentral.com/articles/group-islands-of-contiguous-dates-sql-spackle

2 Likes
6

Sure.

7

@JeffModen

You already know theses things.

For others, if interested, here is a query from my payroll system,
to find continuous absents or leave.

;with ct as
(
select card_code
	, dt
	, flag
	, rn1 = row_number() over (partition by card_code order by card_code, dt) 
	, rn2= row_number() over (partition by card_code order by card_code, flag,dt) 
	, grp = row_number() over (partition by card_code order by card_code, dt) 
	    -   row_number() over (partition by card_code order by card_code, flag,dt) 
from @t t
)
select ct.card_code, startDate = min(dt), endDate=max(dt), absentCount=count(*)
from ct
where ct.flag ='A'                --change here with your flag
group by ct.card_code, grp
having count(*)>=@ra_absent_counts
8

@Mateen ,

Thanks for sharing your code.