Presumption/limitation:
- yearly holidays are stored in a table
- maximum 2 consecutive holidays
Table structure: HOLIDAY_DATE (DATETIME),HOLIDAY_DESC (VARCHAR)
DB Script :
DECLARE @CurrentDay DATETIME
DECLARE @LastWorkingDay DATETIME, @CurrentWeekDay INT
SET @CurrentDay = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)
SET @LastWorkingDay = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)
DECLARE @DaysToLastWorkingDay INT
SET @DaysToLastWorkingDay = 0
SET @LastWorkingDay = DATEADD(day, -1, @LastWorkingDay )
--check if yesterday was holiday
IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, @LastWorkingDay, 101) AS DATETIME))
BEGIN SET @LastWorkingDay= DATEADD(day,-1, @LastWorkingDay)
END
-- adjust for weekends, if needed
SET @CurrentWeekDay = DATEPART(weekday, @LastWorkingDay)
IF @CurrentWeekDay IN (1, 7)
SET @DaysToLastWorkingDay = (CASE @CurrentWeekDay
WHEN 1 THEN -2 -- 1 = Sunday
WHEN 7 THEN -1 -- 7 = Saturday
ELSE 0
END)
SET @LastWorkingDay = DATEADD(day,@DaysToLastWorkingDay, @LastWorkingDay)
IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, @LastWorkingDay, 101) AS DATETIME))
BEGIN
SET @DaysToLastWorkingDay = -1
IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, DATEADD(day, -1, @LastWorkingDay), 101) AS DATETIME))
SET @DaysToLastWorkingDay = @DaysToLastWorkingDay - 1
SET @LastWorkingDay= DATEADD(day,@DaysToLastWorkingDay, @LastWorkingDay)
END
SELECT @LastWorkingDay