在一组日期跨度中找出差距的好方法是什么? [英] What is a good way to find gaps in a set of datespans?

问题描述

在一组日期跨度中寻找差距的方法是什么?

What is a way to find gaps in a set of date spans?

例如，我有以下日期范围:

For example, I have these date spans:

1/ 1/11 - 1/10/11  
1/13/11 - 1/15/11  
1/20/11 - 1/30/11

然后我的开始和结束日期分别为11/7/11和1/14/11.

Then I have a start and end date of 1/7/11 and 1/14/11.

我想知道在1/10/11和1/13/11之间存在间隔，因此开始和结束日期是不可能的.或者我只想返回直到遇到的第一个间隔的日期.

I want to be able to tell that between 1/10/11 and 1/13/11 there is a gap so the start and end date is not possible. Or I want to return only the datespans up to the first gap encountered.

如果可以在SQL Server中完成，那就好了.

If this can be done in SQL server that would be good.

我当时正在考虑遍历每个日期，以了解它是否落在某个日期范围内……如果没有，那么那一天会有间隔.

I was thinking to go through each date to find out if it lands in a datespan... if it does not then there's a gap on that day.

推荐答案

• 跳转至倒数第二个代码段:*I want to be able to tell that between 1/10/11 and 1/13/11 there is a gap so the start and end date is* 不可能.
• 跳转到最后一个代码块:*I want to return only the datespans up to the first gap encountered.*
• Jump to 2nd last code block for: *I want to be able to tell that between 1/10/11 and 1/13/11 there is a gap so the start and end date is* not possible.
• Jump to last code block for: *I want to return only the datespans up to the first gap encountered.*

首先，这是一个要讨论的虚拟表

First of all, here's a virtual table to discuss

create table spans (date1 datetime, date2 datetime);
insert into spans select '20110101', '20110110';
insert into spans select '20110113', '20110115';
insert into spans select '20110120', '20110130';

这是一个查询，它将单独列出日历中的所有日期

This is a query that will list, individually, all the dates in the calendar

declare @startdate datetime, @enddate datetime
select @startdate = '20110107', @enddate = '20110114'
select distinct a.date1+v.number
from spans A
inner join master..spt_values v
  on v.type='P' and v.number between 0 and datediff(d, a.date1, a.date2)
-- we don't care about spans that don't intersect with our range
where A.date1 <= @enddate
  and @startdate <= A.date2

通过此查询，我们现在可以测试是否存在任何差距，方法是 将日历中的天数与预期的天数进行比较

Armed with this query, we can now test to see if there are any gaps, by counting the days in the calendar against the expected number of days

declare @startdate datetime, @enddate datetime
select @startdate = '20110107', @enddate = '20110114'

select case when count(distinct a.date1+v.number)
    = datediff(d,@startdate, @enddate) + 1
    then 'No gaps' else 'Gap' end
from spans A
inner join master..spt_values v
  on v.type='P' and v.number between 0 and datediff(d, a.date1, a.date2)
-- we don't care about spans that don't intersect with our range
where A.date1 <= @enddate
  and @startdate <= A.date2
-- count only those dates within our range
   and a.date1 + v.number between @startdate and @enddate

另一种方法是从@start构建日历 @end提前看看是否有这个日期的跨度

Another way to do this is to just build the calendar from @start to @end up front and look to see if there is a span with this date

declare @startdate datetime, @enddate datetime
select @startdate = '20110107', @enddate = '20110114'
-- startdate+v.number is a day on the calendar
select @startdate + v.number
from master..spt_values v
where v.type='P' and v.number between 0
  and datediff(d, @startdate, @enddate)

-- run the part above this line alone to see the calendar
-- the condition checks for dates that are not in any span (gap)
  and not exists (
    select *
    from spans
    where @startdate + v.number between date1 and date2)

查询返回日期范围@start-@end之间有间隔的所有日期 可以添加TOP 1来查看是否存在差距

The query returns ALL dates that are gaps in the date range @start - @end A TOP 1 can be added to just see if there are gaps

要返回间隔之前的所有记录，请将该查询用作 较大查询中的派生表

To return all records that are before the gap, use the query as a derived table in a larger query

declare @startdate datetime, @enddate datetime
select @startdate = '20110107', @enddate = '20110114'
select *
from spans
where date1 <= @enddate and @startdate <= date2 -- overlaps
  and date2 < ( -- before the gap
    select top 1 @startdate + v.number
    from master..spt_values v
    where v.type='P' and v.number between 0
      and datediff(d, @startdate, @enddate)
      and not exists (
        select *
        from spans
        where @startdate + v.number between date1 and date2)
    order by 1 ASC
)

这篇关于在一组日期跨度中找出差距的好方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！