在SQl中分割日期 [英] Spliting date periods in SQl

问题描述

大家好，

我们的日期范围从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')

推荐答案

我设法将它放入查询中。首先我告诉你代码，然后我会解释。

I managed to put it into a query. First I show you the code, then I'll explain.

; 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中分割日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！