Sql-server – 2 Date Ranges – How many days from one range excluding weekends overlap

datesql server

First – Thanks for your help.

I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends.

Example Data:

Training Dates - 10/16/2014 - 10/28/2014
Fiscal Month Dates - 09/22/2014 -10/21/2014

Number of Days the training overlaps the Fiscal Month Excluding Weekends: 4 Should be the Answer (16th, 17th, 20th, and 21st)

Again Thanks for your help!!

Best Answer

Create date tables or table variables with your ranges and do the join query. Assuming SQL Server as the database, my sample solution is given below.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

Declare @DateTable1 table (Date1 date);

Insert into @DateTable1
select dateadd(day, number, @Range1Begin)
from 
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End;

Declare @DateTable2 table (Date2 date);

Insert into @DateTable2
select dateadd(day, number, @Range2Begin)
from 
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End;


-- Get individual dates
select dt1.Date1 as date, datename(w, dt1.date1) as weekday from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);


-- Get total days
select count(*) as TotalDays from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);

Without declaring table variables, following is the solution.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

-- Get total days
select count(*) as TotalDays from (select dateadd(day, number, @Range1Begin) date1
from 
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End) dt1
inner join (select dateadd(day, number, @Range2Begin) date2
from 
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End) dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);
Related Question