Count days in date range with set of exclusions which may overlap

Question

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.

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

Are the disable ranges inclusive? i.e., would `01/05/2013` to `01/08/2013` count as `3` days or `4`? By default, simply calling `DATEDIFF` is going to include the starting date, but exclude the ending date due to the default time value being `0:00:00` (midnight). — mellamokb, Mar 04 '13 at 21:23
Well only you know what it should be :). The point is when you say a disable range of `01/05/2013` to `01/08/2013`, does that mean the `8th` is also included? — mellamokb, Mar 04 '13 at 21:29
No, for my purposes the second date is when something is 'disabled to' meaning that it is no longer disable at that date. So the 8th does not count. — keithwarren7, Mar 04 '13 at 21:32

score 2 · Answer 1 · edited May 23 '17 at 11:57

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://stackoverflow.com/a/7825036/341117 to fill table with date range

score 2 · Accepted Answer · answered Mar 04 '13 at 21:38

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;

Count days in date range with set of exclusions which may overlap

2 Answers2

Linked