問題描述

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

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?

更簡單地說，我有一個表格，其中包含一組關(guān)閉計費(fèi)的日期范圍，我從一個日期范圍(比如 Jan1 - Jan31)開始，我需要確定該范圍內(nèi)發(fā)生了多少可計費(fèi)天.只是日期的日期差異減去禁用日期的日期差異的總和.但是，禁用日期范圍有可能重疊，即禁用一條記錄中的 Jan5-Jan8 和另一條記錄中的 Jan7-Jan10 - 因此簡單的總和會使 Jan7 重復(fù)計算.排除這些重疊并獲得準(zhǔn)確計數(shù)的最佳方法是什么?

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 之間生成日期.然后將日期與范圍進(jìn)行比較，并找到任何范圍內(nèi)的所有日期.最后，計算結(jié)果中的行數(shù)以獲取禁用日期的計數(shù)(演示):

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;

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