计算日期范围内的天数与可能重叠的排除集

Given the following example query, what is a sound and performant approach to counting the total days in a date range when also given a set of ranges to exclude, given that those ranges may have dates which overlap?

在下面的示例查询中，如果给定一组要排除的范围，考虑到这些范围可能有重叠的日期，那么在一个日期范围内计算总天数的声音和性能方法是什么?

More simply, I have a table with a set of date ranges where the billing is turned off, I start with a date range (say Jan1 - Jan31) and I need to determine how many billable days occured in that range. Simply a datediff of the days minus a sum of the datediff on the disabled days. However, there is a chance that the disabled date ranges overlap, ie disabled Jan5-Jan8 in one record and Jan7-Jan10 in another record - thus a simple sum would double count Jan7. What is the best way to exclude these overlaps and get an accurage count.

更简单地说，我有一个具有一组日期范围的表，其中关闭了账单，我从一个日期范围(比如1月1日- 1月31日)开始，我需要确定在这个范围内发生了多少个可计费天数。简单的日期编辑日减去残疾日期的日期编辑日的总和。然而，有一个可能是，禁用的日期范围有重叠(在一个记录中禁用了Jan5-Jan8)，而在另一个记录中是Jan7- jan10，因此一个简单的数字将会重复计算Jan7。什么是最好的方法来排除这些重叠并得到一个准确的计数。

Declare @disableranges table (disableFrom datetime, disableTo datetime)
insert into @disableranges
select '01/05/2013', '01/08/2013' union
select '01/07/2013', '01/10/2013' union
select '01/15/2013', '01/20/2013'

declare @fromDate datetime = '01/01/2013'
declare @toDate datetime = '01/31/2013'

declare @totalDays int = DATEDIFF(day,@fromDate,@toDate)
declare @disabledDays int = (0 /*not sure best way to calc this*/)

select @totalDays - @disabledDays

2 个解决方案

#1

You can use a recursive CTE to generate dates between @dateFrom and @dateTo. Then compare the dates with the ranges, and find all dates that are in any range. Finally, count the number of rows in the result to get the count of disabled dates (DEMO):

可以使用递归CTE生成@dateFrom和@dateTo之间的日期。然后将日期与范围进行比较，并找到任何范围内的所有日期。最后，计算结果中的行数，得到禁用日期的计数(DEMO):

-- recursive CTE to generate dates
;with dates as (
  select @fromDate as date
  union all
  select dateadd(day, 1, date)
  from dates
  where date < @toDate
)

-- join with disable ranges to find dates in any range
, disabledDates as (
  select date from dates D
  left join @disableranges R
    on D.date >= R.disableFrom and d.Date < R.disableTo
  group by date
  having count(R.disablefrom) >= 1
)

-- count up the total disabled dates
select @disabledDays=count(*) from disabledDates;

#2

Tried this and working okay as far as I am concerned.

就我而言，我试过了，而且还行。

Declare @disableranges table (disableFrom datetime, disableTo datetime)
insert into @disableranges
select '01/05/2013', '01/08/2013' union
select '01/07/2013', '01/10/2013' union
select '01/15/2013', '01/20/2013'

declare @fromDate datetime = '01/01/2013'
declare @toDate datetime = '01/31/2013'

declare @totalDays int = DATEDIFF(day,@fromDate,@toDate) + 1 /*Without +1 it is giving 30 instead of 31*/
declare @disabledDays int = (0 /*not sure best way to calc this*/)
/*Fill temporary table with the given date range.*/
SELECT  DATEADD(DAY, nbr - 1, @fromDate) TempDate INTO #Temp
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @fromDate, @toDate)
/*Check how many dates exists in the disableranges table*/
SELECT @disabledDays=count(*) from #Temp t WHERE 
EXISTS(SELECT * FROM @disableranges 
WHERE t.TempDate BETWEEN disableFrom AND DATEADD(d, -1, disableTo))

select @totalDays /*Output:31*/
select @disabledDays /*Output:10*/
select @totalDays - @disabledDays /*Output:21*/
drop table #Temp

Taken help from the answer https://*.com/a/7825036/341117 to fill table with date range

从答案https://*.com/a/7825036/341117中获得帮助，以填充日期范围表

#1

-- recursive CTE to generate dates
;with dates as (
  select @fromDate as date
  union all
  select dateadd(day, 1, date)
  from dates
  where date < @toDate
)

-- join with disable ranges to find dates in any range
, disabledDates as (
  select date from dates D
  left join @disableranges R
    on D.date >= R.disableFrom and d.Date < R.disableTo
  group by date
  having count(R.disablefrom) >= 1
)

-- count up the total disabled dates
select @disabledDays=count(*) from disabledDates;

#2