No sprawa jest rzeczywiście ciekawa, ale problem polega na tym, że nie można opierać się na poprzednim/następnym rekordzie bo doprowadzi to do błędnych wyników.
Najpierw określmy kolejność wierszy po data_od (tb)
r |
DATA_OD |
DATA_DO |
1 |
2014-03-24 |
2014-07-31 |
2 |
2014-07-01 |
2015-07-12 |
3 |
2014-07-14 |
2015-06-28 |
4 |
2015-04-01 |
2015-04-17 |
5 |
2015-04-10 |
2015-07-09 |
6 |
2015-08-10 |
2015-10-10 |
7 |
2015-10-10 |
2015-12-10 |
8 |
2016-03-10 |
2016-04-10 |
Jako, że nie polegamy na następnym/poprzednim potrzebuje data_do i data_od jak jedna kolumnę - data (tb1):
r |
data |
data_od |
data_do |
1 |
2014-03-24 |
2014-03-24 |
2014-07-31 |
2 |
2014-07-01 |
2014-07-01 |
2015-07-12 |
3 |
2014-07-14 |
2014-07-14 |
2015-06-28 |
4 |
2015-04-01 |
2015-04-01 |
2015-04-17 |
5 |
2015-04-10 |
2015-04-10 |
2015-07-09 |
6 |
2015-08-10 |
2015-08-10 |
2015-10-10 |
7 |
2015-10-10 |
2015-10-10 |
2015-12-10 |
8 |
2016-03-10 |
2016-03-10 |
2016-04-10 |
1 |
2014-07-31 |
2014-03-24 |
2014-07-31 |
2 |
2015-07-12 |
2014-07-01 |
2015-07-12 |
3 |
2015-06-28 |
2014-07-14 |
2015-06-28 |
4 |
2015-04-17 |
2015-04-01 |
2015-04-17 |
5 |
2015-07-09 |
2015-04-10 |
2015-07-09 |
6 |
2015-10-10 |
2015-08-10 |
2015-10-10 |
7 |
2015-12-10 |
2015-10-10 |
2015-12-10 |
8 |
2016-04-10 |
2016-03-10 |
2016-04-10 |
Teraz do tego wyniku trzeba stworzyć unikalne id dla daty (tb2):
id |
r |
data |
data_od |
data_do |
1 |
1 |
2014-03-24 |
2014-03-24 |
2014-07-31 |
2 |
2 |
2014-07-01 |
2014-07-01 |
2015-07-12 |
3 |
3 |
2014-07-14 |
2014-07-14 |
2015-06-28 |
4 |
1 |
2014-07-31 |
2014-03-24 |
2014-07-31 |
5 |
4 |
2015-04-01 |
2015-04-01 |
2015-04-17 |
6 |
5 |
2015-04-10 |
2015-04-10 |
2015-07-09 |
7 |
4 |
2015-04-17 |
2015-04-01 |
2015-04-17 |
8 |
3 |
2015-06-28 |
2014-07-14 |
2015-06-28 |
9 |
5 |
2015-07-09 |
2015-04-10 |
2015-07-09 |
10 |
2 |
2015-07-12 |
2014-07-01 |
2015-07-12 |
11 |
6 |
2015-08-10 |
2015-08-10 |
2015-10-10 |
12 |
7 |
2015-10-10 |
2015-10-10 |
2015-12-10 |
13 |
6 |
2015-10-10 |
2015-08-10 |
2015-10-10 |
14 |
7 |
2015-12-10 |
2015-10-10 |
2015-12-10 |
15 |
8 |
2016-03-10 |
2016-03-10 |
2016-04-10 |
16 |
8 |
2016-04-10 |
2016-03-10 |
2016-04-10 |
Właściwie teraz mam odpowiednio przygotowane dane, aby rozpocząć z nimi działania i "rozpocząć magię" ;)
Grupuje po wierszach aby wyciągnąć zakresy id w jakich mieszczą mi się poszczególne daty (g):
r |
iod |
ido |
1 |
1 |
4 |
2 |
2 |
10 |
3 |
3 |
8 |
4 |
5 |
7 |
5 |
6 |
9 |
6 |
11 |
13 |
7 |
12 |
14 |
8 |
15 |
16 |
Na podstawie grupowania wyciągam maksymalne id dla poszczególnych zakresów (tb4)
r |
iod |
ido |
1 |
1 |
10 |
2 |
2 |
10 |
3 |
3 |
10 |
4 |
5 |
10 |
5 |
6 |
10 |
6 |
11 |
14 |
7 |
12 |
14 |
8 |
15 |
16 |
Teraz wystarczy grupowanie po ido w celu pobrania ostatecznych zakresów (ost):
Wynik to już formalność, zapis SQL-a:
CREATE TABLE #TEST(DATA_OD DATE,DATA_DO DATE)
INSERT INTO #TEST
VALUES
('2014-03-24','2014-07-31')
,('2014-07-01','2015-07-12')
,('2014-07-14','2015-06-28')
,('2015-04-01','2015-04-17')
,('2015-04-10','2015-07-09')
,('2015-08-10','2015-10-10')
,('2015-10-10','2015-12-10')
,('2016-03-10','2016-04-10');
--widok identyfikujacy wiersze
with tb as (
select
row_number() over (order by data_od) r
, *
from
#test
)
--widok pokazujący daty w jednej kolumnie
, tb1 as (
select
r
,data_od data
,data_od
,data_do
from
tb
union all
select
r
,data_do data
,data_od
,data_do
from
tb
)
--widok tworzący id na podstawie tb1
,tb2 as (
select
row_number() over (order by data) id
,*
from
tb1
)
--widok grupujący min/max id w ramach wiersza
, g as (
select
r
,min(id) iod
,max(id) ido
from
tb2
group by
r
)
--widok który pobiera maksymalne "id do"
,tb4 as (
select
g.r
,g.iod
,max(g2.ido) ido
from
g
inner join g g2 on g.iod between g2.iod and g2.ido or g.ido between g2.iod and g2.ido
group by
g.r
, g.iod
)
--ostateczne zakresy id z dat:
,ost as (
select
min(iod) iod
,ido
from
tb4
group by
ido)
--wynik
select
od.data_od
,do.data data_do
from
ost
inner join tb2 od on ost.iod = od.id
inner join tb2 do on ost.ido = do.id
drop table #test
Trzeba by oczywiście protestować ale dla tej porcji danych się sprawdza dając wynik:
data_od |
data_do |
2014-03-24 |
2015-07-12 |
2015-08-10 |
2015-12-10 |
2016-03-10 |
2016-04-10 |