How to exclude dates from a table when searching between two dates?

Question

I have created a query where it can search between two dates to calculate working days excluding bank holidays.

I have created a table with dates inside of bank holidays

How can i create a query where when searching between the two dates, it excludes the dates within the table?

Here is what i have so far...

 DECLARE @StartDate DATETIME
   DECLARE @EndDate DATETIME
-- Calculate the calendar working days below INCLUDING BH
--YEAR/MONTH/DATE
    SET @StartDate = '2016/09/01'
    SET @EndDate = '2016/09/30'
    SELECT
       (DATEDIFF(dd, @StartDate, @EndDate) + 1)
      -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
      -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
       DECLARE @TotalDays INT, @WorkDays INT
       SET @TotalDays = (DATEDIFF(dd, @StartDate, @EndDate) +1)
       print @TotalDays

This isn't MySQL code. It might be SQL Server code. Would you kindly [edit] your tags to reflect the brand and model of table server you are actually using. — O. Jones, Dec 19 '16 at 11:47
I guess you are substracting to many days already: "-(DATEDIFF(wk, @StartDate, @EndDate) * 2)" and the Saturday / Sunday handling are identical, aren't they? And according to the holidays: I would suggest loading a table / temp table / table variable with the holidays beforehand and then select your dates except the holidays. Furthermore: it might be worth to try creating a date table wich covers all dates in the required daterange and then exclude specific values. This is what is done with SimDate in BI scenarios. — Tyron78, Dec 19 '16 at 11:53
So can you show me a solution? I have entered the bank holiday dates into a table called bankholidays with a colun name of Bank_Holidays. — , Dec 19 '16 at 12:37

score 1 · Accepted Answer · answered Mar 30 '17 at 11:59

You can calculate between two dates by manipulating this

DECLARE @sql VARCHAR(500), 
@Region VARCHAR(100) = 'COLUMN2', --It can be Column1, Column2.....
@StartDate DATETIME = '2016-04-10',
@EndDate DATETIME = '2016-04-15'

SET @sql = '
    SELECT
    COUNT(' + @Region + ')
    FROM  bankholidays
    WHERE ' + @Region + ' BETWEEN ''' + CAST(@StartDate AS VARCHAR) + ''' AND ''' + CAST(@EndDate AS VARCHAR) + ''''

EXEC (@SQL)

score 0 · Answer 2 · edited May 23 '17 at 12:25

It looks like you're already using this trick to get the working days (i.e. excluding weekends) between two dates.

NB: This solution assumes that the session's language is English, and that Saturday and Sunday are considered non-working days; these assumptions may not be true for all cultures.

From this post it seems you're already on the right track for excluding bank holidays. i.e.

DECLARE @StartDate DATETIME = '2016-09-01'
DECLARE @EndDate DATETIME = '2016-09-30'

DECLARE @TotalDays INT, @WorkDays INT

SELECT
    @TotalDays = (DATEDIFF(dd, @StartDate, @EndDate) +1)
    , @WorkDays = 
       (DATEDIFF(dd, @StartDate, @EndDate) + 1)  --get the number of working days between 2 dates (including the date itself)
      -(DATEDIFF(wk, @StartDate, @EndDate) * 2)  --get the number of weekend days (i.e. 2 x number of weeks)
      -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --\_then take into account if the current date fell over a weekend
      -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --/
      - coalesce(
      (
          select count(1) 
          from BankHolidays 
          where HolidayDate between @StartDate and @EndDate
      )
      ,0) --and remove any bank holidays which fall during this period

print @TotalDays
print @WorkDays

Note also that this assumes that all of the bank holidays in your table are against weekdays; not weekends (i.e. otherwise those days would be included twice).

In the UK if a bank holiday falls on a weekend (e.g. Christmas this year is on a Sunday), that bank holiday is offset to the next working day (also taking into any other bank holidays; i.e. boxing day on 26th December is a bank holiday in the UK, so the Christmas bank holiday falls on 27th (or falls on 26th and Boxing day's offset to 27th, depending how you prefer to imagine it).
However other cultures may behave differently. Also some people are expected to work weekends, so should the holiday normally fall on the weekend they may still get that as a holiday and not have the offset bank holiday instead; just to add more confusion).

How to exclude dates from a table when searching between two dates?

2 Answers2