問題描述

給定以下示例查詢，考慮到這些范圍可能具有重疊的日期，在給定一組要排除的范圍的情況下，計算日期范圍內總天數的可靠且高效的方法是什么?

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;

這篇關于使用一組可能重疊的排除項計算日期范圍內的天數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！