Computing with DATEADD and taking into account Business Days

[AccessUser22]

I'm stuck. I had a function that I was using in VBA in Microsoft Access to determine a date and take into account holidays and weekends. Basically I need to add one day to a date, but if the date falls on a Friday, it needs to give me Monday's date instead of Saturday.

As well, I have a table that lists out the Holidays used in our system. If Monday ended up being a holiday, it would need to give me Tuesday's date. Does anyone know of a function that can do this? Please let me know. Thanks.

 

[AccessUser22]

Sorry, I forgot to clarify. I am not using Microsoft SQL Server 2005 so I need to move this function from VBA to SQL. Thanks

 

[SQLBill]

You say you aren't using SQL Server 2005, so that means you are using SQL Server 2000?

Have you looked at FAQs: FAQ183-5075 and FAQ183-2968?

-SQLBill

Posting advice: FAQ481-4875

 

[AccessUser22]

It has been a very long day. I meant to say that I am NOW using SQL Server 2005.

I think I'm on the right track with some code I've been working on. It's not quite working yet though:

ALTER FUNCTION fn_BusHolDayDiff(@StartDate SMALLDATETIME,@NumDays as int)
RETURNS SMALLDATETIME
AS
BEGIN

DECLARE @ReturnDate SMALLDATETIME,
@LoopCieling INT, --holds the top value for the loop
@count INT,
@day INT


SET @count = 1
Set @ReturnDate = @StartDate
set @LoopCieling = @NumDays

WHILE @count <= @LoopCieling
BEGIN
SELECT @ReturnDate = DATEADD(day,1,@StartDate)
SELECT @day = DATEPART(dw,@ReturnDate)

-- If it's not a Saturday or Sunday and not a holiday
-- Do not increase the @Count
IF (@day <> 1 AND @day <> 7)
BEGIN
Set @Count = @Count +1
END
else
if NOT EXISTS (SELECT * FROM tblHolidays WHERE holidate= @ReturnDate)
BEGIN
Set @Count = @Count + 1
END
END

RETURN @ReturnDate

END

Currently I have 1/15/2007 in my tblHolidays [Holidate] field. If I run the function using 1/12/2007 and 1 as the increment, it's currently sending back 1/13/2007 which is incorrect. If you have any ideas, please let me know. Thanks

 

[SQLBill]

I'm not following your code........

But first things first....it is CEILING.

Now, let's look at the code this way:

DECLARE @ReturnDate    SMALLDATETIME, 
        @LoopCieling  INT, --holds the top value for the loop
        @count        INT,
        @day         INT
        

SET @count = 1
Set @ReturnDate = '2007-01-12 00:00:00'
set @LoopCieling = @1

WHILE 1 <= 1 
    BEGIN 
    SELECT    @ReturnDate = DATEADD(day,1,'2007-01-12 00:00:00')  --@ReturnDate becomes '2007-01-13 00:00:00'
    SELECT @day = DATEPART(dw,'2007-01-13 00:00:00') --Friday
    
        -- If it's not a Saturday or Sunday and not a holiday
        -- Do not increase the @Count  
    IF (@day <> 1 AND @day <> 7)  
        BEGIN
            Set @Count = @Count +1    
        END
    else
    if NOT EXISTS (SELECT * FROM tblHolidays WHERE holidate= @ReturnDate)        
        BEGIN
            Set @Count = @Count + 1
        END
END

RETURN    '2007-01-13 00:00:00'

One solution for troubleshooting this type of code is to add PRINT commands after SETs and SELECTs. For example:

SET @count = 1
Set @ReturnDate = @StartDate
set @LoopCieling = @NumDays
PRINT @StartDate
PRINT @NumDays
PRINT @Count
PRINT @ReturnDate
PRINT @LoopCieling

WHILE @count <= @LoopCieling 
    BEGIN 
    SELECT    @ReturnDate = DATEADD(day,1,@StartDate)
    SELECT @day = DATEPART(dw,@ReturnDate) 
PRINT @ReturnDate
PRINT @day
<rest of code>

-SQLBill


Posting advice: FAQ481-4875

 

[philhege]

Well, I don't know if this'll help, but I had fun doing it.



declare @returndate datetime,
@weekend bit,
@holiday bit
set @returndate = '03/16/2007'
set @weekend = 1
set @holiday = 1
select @returndate as eval_date, datepart(dw,@returndate) as day_of_week,
@weekend as weekend_flag,
@holiday as holiday_flag


WHILE @weekend = 1 OR @holiday = 1

BEGIN

set @returndate = dateadd(d,1,@returndate)

IF EXISTS(SELECT * FROM tblHolidays WHERE holidate= @ReturnDate)

set @holiday = 1
ELSE
set @holiday = 0

IF datepart(dw,@returndate) IN (1,7)
set @weekend = 1
ELSE
set @weekend = 0


select @returndate as eval_date, datepart(dw,@returndate) as day_of_week,
@weekend as weekend_flag,
@holiday as holiday_flag



END

select @returndate

select * from tblHolidays




Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[AccessUser22]

Hey SQL Bill - I tried your code and first it gave me an error saying that I need to declare @1. So I looked and the LoopCieling needs to be set to the number of days (@NumDays) input into the function. I want to make the function so that whether the user wants 1 business day or 3 business days, it works.

Once I corrected this however, it gets stuck again for me. It just constantly loops.

I'm still working on my code but this is where I'm at now. This code below works fine without the weekends/holidays taken into account. I will try your debug options to see where I'm going wrong.

DECLARE @ReturnDate SMALLDATETIME,
@LoopCieling INT, --holds the top value for the loop
@count INT,
@day INT


SET @count = 1
set @LoopCieling = @NumDays

WHILE @count <= @LoopCieling
BEGIN
SELECT @ReturnDate = DATEADD(day,1,@StartDate)
SELECT @day = DATEPART(dw,@ReturnDate)

-- If it's a Saturday, Sunday or a holiday
-- Increase the number of of the loop by one so it reruns through the loop
if @Day = 1 OR @Day = 7
BEGIN
Set @Count = @Count + 1
set @LoopCieling = @LoopCieling + 1
END
else
IF EXISTS (SELECT * FROM tblHolidays WHERE holidate= @ReturnDate)
BEGIN
Set @Count = @Count + 1
set @LoopCieling = @LoopCieling + 1
END
else
BEGIN
set @Count = @Count + 1
END
END


RETURN @ReturnDate
end

Thanks for your help

 

[AlexCuse]

Hey AccessUser,

I am not sure how many dates at a time you need to do this for, but you can do this without any looping at all. This solution will take any and all dates, but if you are only dealing with the current year it becomes significantly easier. Have a look here:

--make sure week starts on Sunday
set datefirst 7

--prepare test data
create table #DateTest (Date1 Datetime, Date2 Datetime NULL)
create table #Holidays (HDate Datetime)

Insert into #DateTest (Date1)
select getdate()
union all select '19680403'
union all select '19750102'
union all select '19780101'
union all select '19810610'
union all select '19830907'
union all select '19850703'
union all select '19881128'
union all select '19921224'
union all select '19950703'
union all select '19990113'
union all select '20030504'
union all select '20070302'
union all select '20070303'

Insert into #Holidays(HDate)
select '20071225'
union all select '20070101'
union all select '20070704'

--declare table variables
declare @DateEx table (DT datetime)
declare @yrs table (ID int Identity(1,1), yr varchar(4))

--store distinct years to be used in getting dates needed for comparison
--unnecessary if only dealing with current year
INSERT INTO @yrs(yr)
select distinct year(Date1) from #DateTest

--populate @DateEx with all dates for years needed (or current year)
--thanks GOLOM!
INSERT INTO @DateEx ( DT )
Select Distinct DateAdd ( day, Intx.n, z.yr + convert(varchar (4), '0101' )) 
From 
(
SELECT 100*[I1].[num] + 10*[I2].[num] + [I3].[Num] AS n
FROM Integers AS I1, Integers AS I2, Integers AS I3
) Intx
cross join @yrs z

--delete holidays from @DateEx
delete a
from @DateEx a
inner join #Holidays b
on month(a.DT) = month(b.HDate)
and day(a.DT) = day(b.HDate)

--delete weekend days from @DateEx
delete a 
from @DateEx a
where datepart(weekday, a.DT) in (1,7)

--update Date2 in #DateTest to next eligible day
update a
set a.Date2 = b.DT
from #DateTest a
inner join 
(
select z.Date1, min(y.DT) as DT
from #DateTest z
inner join @DateEx y
on z.Date1 < y.DT
group by z.Date1
) b
on a.Date1 = b.Date1

select * from #DateTest

Drop Table #Holidays
Drop Table #DateTest

One thing you need for this is a table that contains all integers from 0-9. Here is the script to create this table:

create table integers (num tinyint)
insert into integers
select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[SQLBill]

My mistake, the:

set @LoopCieling = @1

should have been:

set @LoopCieling = 1

All I did was 'hard code' the values in that you were using, instead of using variables. That should have shown what was happening. The value was January 13, 2007 which is a Friday, so the count never got incremented.

-SQLBill

Posting advice: FAQ481-4875

 

[AccessUser22]

Ahh, I see. DOH! It's always something small right? I kept incrementing off of the start date in the loop and the value never changed. With some more testing and changing my logic around, I've got it working now. Thanks a lot!

 

[SQLBill]

You are welcome.

-SQLBill

Posting advice: FAQ481-4875