Geeks With Blogs
faizan ahmad Usually the things which were not a straight Google

Presumption/limitation:

  1. yearly holidays are stored in a table
  2. 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


Posted on Friday, July 16, 2010 6:16 PM SQL Server | Back to top


Comments on this post: SQL Script to Get Last working Day

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © faizanahmad | Powered by: GeeksWithBlogs.net