X business day

Last Post 24 Apr 2009 08:33 AM by natasha. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
natasha
New Member
New Member

--
24 Apr 2009 07:22 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 @ReturnVal
natasha
New Member
New Member

--
24 Apr 2009 08:33 AM
Never mind. Ihave my code working. I fnay body is interested I am pasting my code here. If anybody has a better solution then I am definitely interested 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
You are not authorized to post a reply.

Acceptable Use Policy