Sql查询需要包含银行假日

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.

正如你所看到的，这个月结束日+ 4个工作日。

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.

任何指针都将被非常感激。

1 个解决方案

#1

Build a calendar table.

建立一个日历表。

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

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