March 5, 2013 at 8:30 am
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
March 5, 2013 at 9:40 am
Your tally table starts at 1 not 0.
March 5, 2013 at 9:56 am
Thanks Lynn.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy