DATEADD function but only for Work Days.

[Pradeep1210]

I need a SQL function that adds days, but only the workdays. It should exclude holidays, maintainence days/hours, special days etc.
This is for calculating the estimated finish time of any task being allotted to any employee.

For the work days, I already have a table which can be created with the following sample code:

USE tempdb
GO
 
create table WorkSchedule
(
DOW INT,                -- day of week
StartTime DateTime,        -- work start time
EndTime DateTime,        -- work end time
WorkDay bit default 1    -- 1=work day, 0=off day.
)
 
insert into WorkSchedule
      select 1, '1900-01-01 08:00:00', '1900-01-01 17:00:00', 0    -- sunday
union select 2, '1900-01-01 08:00:00', '1900-01-01 17:00:00', 1    -- monday
union select 3, '1900-01-01 08:00:00', '1900-01-01 17:00:00', 1    -- tuesday
union select 4, '1900-01-01 08:00:00', '1900-01-01 17:00:00', 1    -- wednesday
union select 5, '1900-01-01 08:00:00', '1900-01-01 17:00:00', 1    -- thursday
union select 6, '1900-01-01 08:00:00', '1900-01-01 17:00:00', 1    -- friday
union select 7, '1900-01-01 08:00:00', '1900-01-01 17:00:00', 0    -- saturday
 
union select -1, '2011-03-01 08:00:00', '2011-01-03 17:00:00', 0    -- off day on 1-Mar-11
union select -1, '2011-03-15 08:00:00', '2011-03-17 17:00:00', 0    -- off days on 15 to 17th march.
union select -1, '2011-03-25 12:00:00', '2011-03-25 17:00:00', 0    -- half day off on 20-march
 
select * from WorkSchedule
GO

The table has 7 fixed rows with DOW = 1 to 7 which can't be deleted, only date range or WorkDay=true/false can be set. This is regular schedule and applies to all dates. So for such records, only the Time part is relevant.

If a record has DOW = -1, it is an exception (either exceptional workday i.e. emergency etc. or a holiday i.e. govt holidays or festivals etc.) and its full date is relevant.

Now I need a function that takes a current date and minutes to add, and returns me a date with work minutes added to it.

CREATE FUNCTION AddWorkMinutes
(    @StartDate DATETIME,    -- task start date.
    @MinutesToAdd INT        -- number of minutes alloted.
) 
RETURNS DATETIME 
AS 
BEGIN
    DECLARE @ReturnDate DATETIME
 
    -- calcualate the return date here
 
    -- should work similar to this, but ignore minutes falling in off days.
    -- select @ReturnDate = DATEADD(mi, @MinutesToAdd, @StartDate)
 
    RETURN @ReturnDate 
END

At present, I'm looping through each minute from @StartDate onwards and testing it for positivity, until I have spanned @MinutesToAdd minutes. This is OK for small @MinutesToAdd values, but where say when I need to add a few months, the speed is utterly slow.

But I think there must be a better way than looping and spanning through each minute. Anyone has any better ideas?