CODEWITHSUNDEEP
sql-servert-sql
keithwarren7
keithwarren7

Reputation: 14278

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

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

Upvotes: 2

Views: 1732

Answers (2)

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9158

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

Upvotes: 2

mellamokb
mellamokb

Reputation: 56769

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;

Upvotes: 2

Related Questions