sql - 解决连续性的日期范围(GAPS AND ISLANDS)

Question

我有一个如下表，其中 X 列是给定开始和结束期间的商品价格。

X    START_DATE     END_DATE
------------------------------    
1    01-01-2014     01-01-2016
2    01-04-2014     01-05-2014
3    01-07-2014     01-08-2014

然而，第一个条目定义了更大的时间范围，只有当项目价格没有定义为每月时间段时才应该考虑，类似于第二个和第三个记录，或者当某些范围缺失时。现在所需的输出是

X    START_DATE      END_DATE
------------------------------    
1    01-01-2014     01-04-2014
2    01-04-2014     01-05-2014
1    01-05-2014     01-07-2014
3    01-07-2014     01-08-2014
1    01-08-2014     01-01-2016

我怎样才能做到这一点？

Answer 1

勾选这个，如果你开心就+1

-- Data Samples
declare @X table ( Price int, datefrom datetime, dateto datetime)
insert @X values ( 1, '1.1.2014','1.1.2016'),(2,'1.4.2014','1.5.2014'),(3,'1.7.2014','1.8.2014');

-- Check samples
select * from @X;

-- Query
with Dat as ( 
            select datefrom from @X
            union
            select dateto from @X 
)
, Periods as ( 
            select datefrom,dateto = LEAD(datefrom,1) over (order by datefrom) 
            from Dat
)
,val as ( select Pr.*,P.*
            from Periods P 
            cross apply ( select top 1 Price from @X
                        where P.datefrom between datefrom and dateto - 0.000001
                        order by DATEDIFF(day,datefrom,dateto)
            ) Pr

)

select * from val

输出

Price       datefrom                dateto
----------- ----------------------- -----------------------
1           2014-01-01 00:00:00.000 2016-01-01 00:00:00.000
2           2014-04-01 00:00:00.000 2014-05-01 00:00:00.000
3           2014-07-01 00:00:00.000 2014-08-01 00:00:00.000

(3 row(s) affected)

Price       datefrom                dateto
----------- ----------------------- -----------------------
1           2014-01-01 00:00:00.000 2014-04-01 00:00:00.000
2           2014-04-01 00:00:00.000 2014-05-01 00:00:00.000
1           2014-05-01 00:00:00.000 2014-07-01 00:00:00.000
3           2014-07-01 00:00:00.000 2014-08-01 00:00:00.000
1           2014-08-01 00:00:00.000 2016-01-01 00:00:00.000

(5 row(s) affected)