问题描述
大家好,
我们的日期范围从2016-01-02到2016-01-30
在@premiumtable中保存了日期。
我需要使用@premiumtable中的日期范围来分割日期范围
我需要输出如下表中的startdate ENddate
Hi All,
we are having a date range period from 2016-01-02 to 2016-01-30
there are date periods saved in @premiumtable .
I need to split the date range using the date ranges in @premiumtable
I need output as below in a table as startdate ENddate
start_date | ENd_date
2016-01-02 | 2016-01-04
2016-01-05 | 2016-01-10 -date from premium table
2016-01-11 | 2016-01-14
2016-01-15 | 2016-01-20 -date from premium table
2016-01-21 | 2016-01-30
-----------------------------------------------------------------------
declare @startdate datetime ='2016-01-02'
declare @enddate datetime = '2016-01-30'
declare @premiumtable table
(
startdate datetime,
enddate datetime
)
insert into @premiumtable values ('2016-01-05','2016-01-10')
insert into @premiumtable values ('2016-01-15','2016-01-20')
推荐答案
; with cte1 as
(
select row_number() over(order by startdate) id, * from @premiumtable
)
, cte2 as
(
select startdate, enddate from cte1
union all
select dateadd(day, 1, a.enddate) as startdate, dateadd(day, -1, b.startdate) as enddate
from cte1 a inner join cte1 b on a.id+1 = b.id
union all
select null as startdate, dateadd(day, -1, min(startdate)) as enddate
from @premiumtable
union all
select dateadd(day, 1, max(enddate)) as startdate, null as enddate
from @premiumtable
)
select
case when startdate < @startdate or startdate is null then @startdate else startdate end as startdate,
case when enddate > @enddate or enddate is null then @enddate else enddate end as enddate
from cte2
where (enddate >= @startdate or enddate is null)
and (startdate <= @enddate or startdate is null)
order by startdate
问题是你没有范围,你需要创建它们。仔细查看cte2,联合中有四个查询。
1)从@premiumtable中选择现有范围。
2)填写@premiumtable的空白。
3)在@premiertable中列出的第一个范围之前添加领先的开放范围。
4)在@premiertable中列出的最后一个范围之后添加尾随开放范围。
一旦你拥有了这套完整的范围,你只需根据你的参数过滤这些范围。选择部分或全部在@startdate和@enddate中的所有范围。当然你需要削减最外面的范围 - 这是由CASE完成的。
[]
享受。
The problem is you don't have the ranges, you need to create them. Look carefully at cte2, there are four queries in the union.
1) Select existing ranges from @premiumtable.
2) Fill in the gaps in @premiumtable.
3) Add the leading open range before the first range listed in @premiertable.
4) Add the trailing open range after the last range listed in @premiertable.
Once you have this complete set of ranges you just simply filter those based on your parameters. Select all ranges that are partially or entirely within @startdate and @enddate. Of course you need to cut the outermost ranges - this is done by the CASEs.
Here is the fiddle.[^]
Enjoy.
这篇关于在SQl中分割日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!