Last business day of the month; Partial holiday calendar and tally table

Question

Post reply

Last business day of the month; Partial holiday calendar and tally table

Chrissy321

SSCoach

Points: 15750
More actions
March 5, 2013 at 8:30 am

#271952

Hi Folks,
I am trying to perform some if/else logic on the last business day of the month. I have a third- party provided partial holiday calendar, just the holidays.
When I hard code the date, SET @StartDate = '2013-03-28', I am not getting my expected result
Any suggestions?
--Create the tally table if you don't have one-Courtesy Jeff Moden
--===== Create and populate the Tally table on the fly
/*
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
*/
CREATE TABLE #Holidays (HolidayDate datetime, IsHoliday char(1))
INSERT INTO #Holidays (HolidayDate,IsHoliday) VALUES('2013-02-18 00:00:00.000', 'Y')
INSERT INTO #Holidays (HolidayDate,IsHoliday) VALUES('2013-03-29 00:00:00.000', 'Y');
DECLARE @StartDate DATE
DECLARE @LastBusinessDayMonth DATE
SET @StartDate = GETDATE()--@LastBusinessDayMonth seems to be set properly here
SET @StartDate = '2013-03-28'--Doesn't work here
SET @LastBusinessDayMonth =
(
SELECT TOP 1
MAX((DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n))
FROM TALLY T
LEFT JOIN #Holidays H
ON H.HolidayDate = (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n)
WHERE
DATEPART ( mm , @StartDate ) = DATEPART ( mm , (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n) ) AND --Days in the current month
DATEPART ( yy , @StartDate ) = DATEPART ( yy , (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n) )AND --Days in the current year
HolidayDate IS NULL AND --Exclude holidays
DATEPART(WEEKDAY, (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n)) NOT IN (7,1)--Exclude weekends
)
PRINT @StartDate
PRINT @LastBusinessDayMonth
IF @LastBusinessDayMonth = @StartDate
BEGIN
PRINT 'LastBusinessDay'
END
ELSE
BEGIN
PRINT 'NotLastBusinessDay'
END
DROP TABLE #Holidays