April 24, 2009 at 9:28 am
I need to find out 3rd to last business day of the month excluding holidays and weekends. For example if last day / date of the month for April is Thursday, 4/30/3009 then I need to fetch the date as going back three business day which should be Tuesday / 4/27/2009.
For May last day is Sunday so it is weekend and so is second last day Saturday so correct day should be 5/27/2009. I have my code working the only issue is with August and November for year 2009. I have a function which checks for holidays (Public holidays and weekends).
Here is the code:
declare
@day datetime,
@Rtn int,
@LoopCount int
select @day = '5/10/2009'
print @day
select @day = dateadd(day,-1,(dateadd(day, 1- datepart(day, @day), @day)))
SET @Rtn = dbo.fn_CheckForHoliday(@Day)
print @day
print @rtn
select @day =
CASE @rtn
WHEN 1 THEN dateadd(day,-4,@day)
WHEN 2 THEN dateadd(day,-3,@day)
WHEN 0 THEN dateadd(day,-2,@day)
END
print @day
SET @Rtn = dbo.fn_CheckForHoliday(@Day)
select @loopcount = 1
WHILE ((@Rtn <> 0) AND (@LoopCount < 7))
BEGIN
SET @day = DATEADD(d,-1,@day)
SET @Rtn = dbo.fn_CheckForHoliday(@Day)
SET @LoopCount = @LoopCount + 1
print @rtn
END
print @day
Here is function to check Holidays.
FUNCTION [dbo].[fn_CheckForHoliday](@TodayDate DateTime)
RETURNS int
AS
BEGIN
DECLARE @Year Int,
@TodayDay DateTime,
@HDNewYear DateTime,
@HDMLK DateTime,
@HDPresident DateTime,
@HDMemorial DateTime,
@HDFourth DateTime,
@HDLabor DateTime,
@HDVets DateTime,
@HDThanks DateTime,
@HDChristmas DateTime,
@HDColumbus DateTime,
@ReturnValue Int
SELECT @TodayDay = CONVERT(DateTime,CONVERT(VarChar(10),@TodayDate,110))
SELECT @Year = DATEPART(yy,@TodayDate)
-- Set Holidays
SELECT @HDNewYear = '1/1/' + CONVERT(VarChar(10),@Year)
SELECT @HDMLK = DATEADD(dd,(DATEDIFF(dd,'12/30/1899','1/5/' + CONVERT(VarChar(10),@Year))%7)*-1,'1/21/' + CONVERT(VarChar(10),@Year))
SELECT @HDPresident = DATEADD(dd,(DATEDIFF(dd,'12/30/1899',DATEADD(dd,-2,'2/21/' + CONVERT(VarChar(10),@Year)))%7)*-1,'2/21/' + CONVERT(VarChar(10),@Year))
SELECT @HDMemorial = DATEADD(dd,(DATEDIFF(dd,'12/30/1899',DATEADD(dd,-2,'5/31/' + CONVERT(VarChar(10),@Year)))%7)*-1,'5/31/' + CONVERT(VarChar(10),@Year))
SELECT @HDFourth = '7/4/' + CONVERT(VarChar(10),@Year)
SELECT @HDColumbus = DATEADD(dd,(DATEDIFF(dd,'12/30/1899',DATEADD(dd,-2,'10/14/' + CONVERT(VarChar(10),@Year)))%7)*-1,'10/14/' + CONVERT(VarChar(10),@Year))
SELECT @HDLabor = DATEADD(dd,(DATEDIFF(dd,'12/30/1899',DATEADD(dd,-2,'9/7/' + CONVERT(VarChar(10),@Year)))%7)*-1,'9/7/' + CONVERT(VarChar(10),@Year))
SELECT @HDVets = '11/11/' + CONVERT(VarChar(10),@Year)
SELECT @HDThanks = DATEADD(dd,(DATEDIFF(dd,'12/30/1899',DATEADD(dd,-5,'11/28/' + CONVERT(VarChar(10),@Year)))%7)*-1,'11/28/' + CONVERT(VarChar(10),@Year))
SELECT @HDChristmas = '12/25/' + CONVERT(VarChar(10),@Year)
SELECT @ReturnValue = 0
-- Return Values
-- 0 - No Holiday or Weekend
-- 1 - Sunday
-- 2 - Saturday
-- 3 - New Year's Day
-- 4 - MLK Day
-- 5 - President's Day
-- 6 - Memorial Day
-- 7 - Fourth of July
-- 8 - Labor Day
-- 9 - Veteran's Day
-- 10 - Thanksgiving
-- 11 - Christmas
-- 12 - Columbus
IF DATEPART(dw, @TodayDate) = 1
SELECT @ReturnValue = 1
IF DATEPART(dw, @TodayDate) = 7
SELECT @ReturnValue = 2
IF @TodayDay = @HDNewYear
SELECT @ReturnValue = 3
IF @TodayDay = @HDMLK
SELECT @ReturnValue = 4
IF @TodayDay = @HDPresident
SELECT @ReturnValue = 5
IF @TodayDay = @HDMemorial
SELECT @ReturnValue = 6
IF @TodayDay = @HDFourth
SELECT @ReturnValue = 7
IF @TodayDay = @HDLabor
SELECT @ReturnValue = 8
IF @TodayDay = @HDVets
SELECT @ReturnValue = 9
IF @TodayDay = @HDThanks
SELECT @ReturnValue = 10
IF @TodayDay = @HDChristmas
SELECT @ReturnValue = 11
IF @TodayDay = @HDColumbus
SELECT @ReturnValue = 12
RETURN(@ReturnValue)
END
TIA for your help,
Natasha
April 24, 2009 at 10:36 am
Never mind. My code is working. If anyone is interested here is the code. If you have a better solution/ suggestion I am interested in that too.
declare
@day datetime,
@Rtn int,
@LoopCount int,
@DayWeek int
select @day = '1/25/2011'
select @day = dateadd(day,-1,(dateadd(day, 1- datepart(day, @day), @day)))
print @day
select @DayWeek =datepart(dw,@day)
print @DayWeek
select @day =
case
when @DayWeek between 1 and 3 then dateadd(day,-4,@day)
when @DayWeek between 4 and 6 then dateadd(day,-2,@day)
when @DayWeek = 7 then dateadd(day,-3,@day)
End
print @day
SET @Rtn = dbo.fn_CheckForHoliday(@Day)
print @rtn
IF @rtn 0
set @day = dateadd(day,-1,@day)
print @day
April 25, 2009 at 1:00 am
If you don't already have one, you will really find it to your advantage to create a calendar table and prepopulate several years in advance with dates, flagged as weekends or holidays where appropriate. (There are a number of good articles out there if you search on CALENDAR.)
Once you have a calendar table in place, queries to determine dates become MUCH simpler and easier to read.
declare @start datetime
set @start = '5/1/2009'
-- I'm just using a cte to simulate a calendar table.
-- A real one would be kept years in advance, be properly indexed, and use an abbreviated schema.
--
;with cteCal (xdate) as
(select top 31 @start-row_number() over (order by id) from syscolumns)
,Calendar (xdate,daytype) as
(select xdate, case when datepart(dw,xdate) in (1,7) then 'weekend' else 'business' end from cteCal)
---
--- Disregard all of the above as just being created to simulate a calendar table
--- Given that you have a calendar table, the following is all you need
---
,cteSolution (xdate,seq) as (select xdate, row_number() over(order by xdate DESC) from Calendar
where xdate >= '4/1/2009' and xdate < '5/1/2009'-- just look at April
and daytype = 'business') -- business days only
--
select xdate from cteSOLution where seq = 4-- three days back from last business day.
--
--
As you can see, the solution simply uses ROW_NUMBER() to count backwards through the calendar for the month of April, looking only at business days. The last business day would be number 1, so the third day back is number 4. This took me only a few minutes to code and it is much easier to understand and maintain.
Would you mind posting the code for your function to check for holidays?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 27, 2009 at 6:40 am
Bob,
Thank you for your response and a better solution. My code to check holiday function is already posted in my first posting.
Thanks again,
Natasha
April 27, 2009 at 8:38 am
Why so it is! Sorry. Good luck with implementing the Calendar table. You won't regret it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 13, 2010 at 8:52 am
I tried creating your function. It is throwing an error and I can't see why at all:
Msg 102, Level 15, State 1, Procedure fn_CheckForHoliday, Line 76
Incorrect syntax near ')'.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
??
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply