I'm creating a query which will adjust the date excluding holidays and weekends.
Example data:
Adjusted Date | Adjusted Date(Excluding Holidays and weekends)
02/06/16 | 02/09/16
On my example, The date is a weekend and adjusted date becomes Feb 9 because Feb 8 is a holiday, so it needs to adjust so that the adjusted date would be a working day. Currently, I have a separated table of all the weekends and holidays in a fiscal year.
select case when (
select count(dbo.WeekendsHoliday.[Weekends & Holidays])
from dbo.WeekendsHoliday
where dbo.WeekendsHoliday.[Weekends & Holidays]
= case when convert(time, [Time Received]) > convert(time, '5:00:00 PM')
then dateadd(day, 1, [Date Received])
else [Date Received]
end
) > 0
then case (datename(DW,
case when convert(time, [Time Received]) > convert(time, '5:00:00 PM')
then dateadd(day, 1, [Date Received])
else [Date Received]
end))
when 'Saturday'
then dateadd(day, 2,
case when convert(time, [Time Received]) > convert(time, '5:00:00 PM')
then dateadd(day, 1, [Date Received])
else [Date Received]
end)
else dateadd(day, 1,
case when convert(time, [Time Received]) > convert(time, '5:00:00 PM')
then dateadd(day, 1, [Date Received])
else [Date Received]
end)
end
end as [Adjusted Date Excluding holidays and weekends]
What happens here is if the holiday is 2 consecutive days (Thursday and Friday), adjusted date would be Saturday which is still not valid because it's a weekend.
Adjusted date is an alias here
I suggest to create a function that recursively verify the next working day based on the table that contains weekends and holidays. The advantage of this approach is that it is a reusable function whenever you need it.
This function receives the date and time. (Based on the code in your question) if the time is after 5pm, adds a day. After, continues checking if the date is not within weekends or holidays until find the next working day:
CREATE FUNCTION dbo.adjustedDate(@dateReceived DATETIME, @timeReceived TIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @adjustedDate DATETIME = @dateReceived
-- Verify time to add 1 day to @adjustedDate
IF @timeReceived IS NOT NULL
IF @timeReceived > CONVERT(TIME, '5:00:00 PM')
SET @adjustedDate = DATEADD(DAY, 1, @adjustedDate)
-- Continue adding 1 day to @adjustedDate recursively until find one date that is not a weekend or holiday
IF EXISTS(SELECT [Weekends & Holidays]
FROM dbo.WeekendsHoliday
WHERE [Weekends & Holidays] = @adjustedDate)
SET @adjustedDate = dbo.adjustedDate(DATEADD(DAY, 1, @adjustedDate), NULL)
RETURN @adjustedDate
END
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments