Sql query needs to include bank holidays

Question

I have the follwoing query:

set language 'english'

DECLARE @MyDate DATETIME

SET @MyDate = dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate()),0))

SELECT ReportEndDate = DATEADD(dd, CASE 
        WHEN DATENAME(weekday,@MyDate) = 'Saturday' THEN 5 
        WHEN DATENAME(weekday,@MyDate) IN ('Monday','Sunday') THEN 4 
        ELSE 6 END, @MyDate)

Which as you can see retuns the month end day + 4 working days.

I need to expand this to include Christmas and New Year. So that the above query takes those bank holidays into consideration when working out the 4th working day of the new month.

Any pointers would be much apreciated.

Answer 1

Build a calendar table.

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html