使用一组可能重叠的排除项计算日期范围内的天数

2021-09-10 00:00:00 tsql sql-server

给定以下示例查询，考虑到这些范围可能具有重叠的日期，在给定一组要排除的范围的情况下，计算日期范围内总天数的可靠且高效的方法是什么?

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?

更简单地说，我有一个表格，其中包含一组关闭计费的日期范围，我从一个日期范围(比如 Jan1 - Jan31)开始，我需要确定该范围内发生了多少可计费天.只是日期的日期差异减去禁用日期的日期差异的总和.但是，禁用日期范围有可能重叠，即禁用一条记录中的 Jan5-Jan8 和另一条记录中的 Jan7-Jan10 - 因此简单的总和会使 Jan7 重复计算.排除这些重叠并获得准确计数的最佳方法是什么?

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.

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

推荐答案

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

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):

-- 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;

相关文章