-- First the number based series
declare @t table ( data int )
insert into @t (data) values ( 3 )
insert into @t (data) values ( 4 )
insert into @t (data) values ( 7 )
insert into @t (data) values ( 8 )
insert into @t (data) values ( 9 )
insert into @t (data) values ( 13 )
insert into @t (data) values ( 15 )
select * from @t order by data
/* gives the result:
data
-----------
3
4
7
8
9
13
15
*/
-- Now the date based series
declare @d table ( start_date datetime , end_date datetime , id int identity)
declare @x datetime
set @x = convert(datetime, convert(varchar(10), getdate(), 120), 120)
insert into @d (start_date, end_date ) select @x, @x
insert into @d (start_date, end_date ) select @x + 1, @x + 3
insert into @d (start_date, end_date ) select @x + 5, @x + 8
insert into @d (start_date, end_date ) select @x + 7, @x + 10
insert into @d (start_date, end_date ) select @x + 10, @x + 11
insert into @d (start_date, end_date ) select @x + 15, @x + 17
insert into @d (start_date, end_date ) select @x + 14, @x + 16
select *
from @d
order by start_date, end_date
/* gives the result :
start_date end_date id
----------------------- ----------------------- -----------
2010-10-21 00:00:00.000 2010-10-21 00:00:00.000 1
2010-10-22 00:00:00.000 2010-10-24 00:00:00.000 2
2010-10-26 00:00:00.000 2010-10-29 00:00:00.000 3
2010-10-28 00:00:00.000 2010-10-31 00:00:00.000 4
2010-10-31 00:00:00.000 2010-11-01 00:00:00.000 5
2010-11-04 00:00:00.000 2010-11-06 00:00:00.000 7
2010-11-05 00:00:00.000 2010-11-07 00:00:00.000 6
*/
;with data as (
select t.*
, row_number() over ( order by data desc ) rx
from @t t
)
select tc.data + 1 start_gap
, tn.data - 1 end_gap
, row_number() over (order by tc.data asc) rn
from data tc
left join data tn on tn.rx = tc.rx -1
where ( tn.data <> tc.data + 1 and tc.rx > 1 )
order by tc.rx desc
/*
start_gap end_gap rn
----------- ----------- --------------------
5 6 1
10 12 2
14 14 3
*/
; with d1 as (
select m.end_date
, row_number() over (order by m.end_date ) rn
from @d m
where not exists ( select null
from @d o
where o.start_date <= m.end_date
and o.end_date >= m.end_date
and o.id <> m.id
)
group by m.end_date
)
, d2 as (
select m.start_date
, row_number() over (order by m.start_date ) rn
from @d m
where not exists ( select null
from @d o
where o.start_date <= m.start_date
and o.end_date >= m.start_date
and o.id <> m.id
)
group by m.start_date
)
select d1.end_date gap_start, d2.start_date gap_end, d1.rn
from d1
join d2
on d1.rn = d2.rn - 1
/*
gap_start gap_end rn
----------------------- ----------------------- --------------------
2010-10-21 00:00:00.000 2010-10-22 00:00:00.000 1
2010-10-24 00:00:00.000 2010-10-26 00:00:00.000 2
2010-11-01 00:00:00.000 2010-11-04 00:00:00.000 3
*/
;with data as (
select t.*
, row_number() over ( order by data asc ) rn
, row_number() over ( order by data desc ) rx
from @t t
)
, data2 as (
select tc.data, tc.rn, tc.rx, tp.data prev_data
, row_number() over (order by tc.rn) r2
from data tc
left join data tp
on tp.rn = tc.rn - 1
where ( tp.rn is null or tp.data <> tc.data - 1 )
)
select tc.data range_start
, isnull(tn.prev_data, (select max(data) from data )) range_end
, tc.r2 rn
from data2 tc
left join data2 tn
on tn.r2 = tc.r2 + 1
order by tc.rn
/*
range_start range_end rn
----------- ----------- --------------------
3 4 1
7 9 2
13 13 3
15 15 4
*/
; with d1 as (
select m.end_date
, row_number() over (order by m.end_date ) rn
from @d m
where not exists ( select null
from @d o
where o.start_date <= m.end_date
and o.end_date >= m.end_date
and o.id <> m.id
)
group by m.end_date
)
, d2 as (
select m.start_date
, row_number() over (order by m.start_date ) rn
from @d m
where not exists ( select null
from @d o
where o.start_date <= m.start_date
and o.end_date >= m.start_date
and o.id <> m.id
)
group by m.start_date
)
, d3 as (
select d1.end_date gap_start, d2.start_date gap_end, d1.rn
from d1
join d2
on d1.rn = d2.rn - 1
union all
select null, min(start_date), 0 from @d
union all
select max(end_date), null, (select max(rn) from d2) from @d
)
select dc.rn + 1 rn, dc.gap_end range_start, dn.gap_start range_end
from d3 dc
join d3 dn
on dn.rn = dc.rn + 1
order by dn.rn
/*
rn range_start range_end
-------------------- ----------------------- -----------------------
1 2010-10-21 00:00:00.000 2010-10-21 00:00:00.000
2 2010-10-22 00:00:00.000 2010-10-24 00:00:00.000
3 2010-10-26 00:00:00.000 2010-11-01 00:00:00.000
4 2010-11-04 00:00:00.000 2010-11-07 00:00:00.000
*/
select *
from @d m
where exists ( select null
from @d o
where o.start_date <= m.end_date
and o.end_date >= m.start_date
and o.id <> m.id
)
/*
start_date end_date id
----------------------- ----------------------- -----------
2010-10-26 00:00:00.000 2010-10-29 00:00:00.000 3
2010-10-28 00:00:00.000 2010-10-31 00:00:00.000 4
2010-10-31 00:00:00.000 2010-11-01 00:00:00.000 5
2010-11-05 00:00:00.000 2010-11-07 00:00:00.000 6
2010-11-04 00:00:00.000 2010-11-06 00:00:00.000 7
*/
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
Commented:
Author
Commented:https://www.experts-exchange.com/questions/28408382/Merging-daily-records-to-range-of-dates-in-SQL.html?anchorAnswerId=39988437#a39988437