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?

[stlaural]

the way I see it you will need to have a list of dates for the holidays and maintenance days. But for the weekdays you could use this. It doesn't answer 100% of the question but it could help simplifying it a little.


For the minutes thing, if your work days are always 8h, then it means you work 8*60 minutes. I would simply divide the number of minutes to add by (8*60) to get the number of total days out of it. Add those days to the date and if there are some minutes remaining then add those to the date too.

[Pradeep1210]

No... I don't need the week name.
The table already has regular schedule (DOW > 0) and holiday and maintainence days (DOW < 0). I need to add minutes to given date such that it falls between DOW>0 and WorkDay=1, but not between DOW<0 and WorkDay=0.

At present I'm doing something like this in the body of the function. But as you can see I'm spanning minute by minute which I want to replace with a set-based operation, or any other faster way.

WHILE (@MinutesAdded < @MinutesToAdd)
BEGIN
    SET @ReturnDate = DATEADD(mi, 1, @ReturnDate)
    IF  EXISTS(SELECT 1 FROM WorkSchedule WHERE dbo.GetTime(@ReturnDate) BETWEEN dbo.GetTime(StartTime) AND dbo.GetTime(EndTime) AND DOW = DATEPART(dw, @ReturnDate) AND WorkDay = 1)
        AND NOT EXISTS(SELECT 1 FROM WorkSchedule WHERE @ReturnDate BETWEEN StartTime AND EndTime AND DOW = -1 AND WorkDay = 1)
            SET @MinutesAdded = @MinutesAdded + 1
END

[Pradeep1210]

Also the regular workdays may not always be 8 hours. It must be referred from the table. It may vary and administrators are responsible for maintaining it.

[szlamany]

Got your pm - quick glance - I can give you more later tomorrow AM

You need to get all rows into a table - so that all dates are really represented.

Along with an IDENTITY column (or ROW_NUMBER() if you want to build it at run-time in the sproc)

Then you do a SUM() with a sub-query of all prior days - so you know each date - from start date - with how many minutes are START-to-DATE to "that specific row".

Then it's a matter if MIN(SEQ) where TotalMinutes>=WhatWeWantForMinutes

[leinad31]

If you want is set based then it will be comparable to a running balance query.

Unfortunately, the layout of your data in Workschedule is not ideal for set based operation. For it to become set based (can be retrieved via 1 SQL statement, and not T-SQL), you will have to generate rows per date on the fly rather than just reading the dates from a pre-populated calendar table, e.g. setup a recordset returning sproc that returns the days of the year with relevant info per day. This is additional CPU overhead per query execution.

[Pradeep1210]

I had thought of writing all the working time slots to table first, but then discarded that idea due to following reasons:

1. How will I provide any user interface for the administrators to manage it? They can't enter all dates of the year. And it is also cumbersome for them if I generate all the records for them looking at hundreds of records instead of just 7 in this case.

2. The logic for removing the exclusion time slots (holidays, maintenance hours, other non-working time etc.) would be too complex. At present it is easy - we just enter the time-slots in the table and don't do any calculation here. But then we pay for it in terms of performance later.

3. There's no limit when to stop, if I auto-generate the time-slots. End of the month? End of the year? What happens if work is assigned say in mid December for 2 months, and the time schedule for next year is absent?

[leinad31]

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?

As mentioned before, table structure doesn't allow purely set based calculation. You really need to loop.

Convert MinutesToAdd into days then do a case/switch
case A: if it is only for several days (week or so) then perform existing algorithm, iterate per day.

case B: if it is of considerable duration (more than 4 weeks) then extend DOW in (1,2,3,4,5,6,7) concept so you will iterate first per 4 weeks then remainder is iterated per day.

Pre-calculate total work time (in mins) per 4 weeks and insert that into WorkSchedule (not committed, e.g. insert DOW = 28 record). The remainder (from less than 1 day to less than 28 days + minutes) is iterated as in case A.

After you have done iterations, either via case A or B, you now have a tentative end date. You will adjust end date according to holidays within start date to tent. end date (select where DOW < 0 and so on criteria for datetime), e.g. if tent. end date was Mar 7 3:00 PM and there are 2 holidays since start date to Mar 7 3:00PM then adjusted/final end date is Mar 9 3:00PM.

Process can be broken into 3 procedures

Proc A, main entry point. Creates DOW=28 (total minutes for 28 days unadjusted by holidays) if it doesn't exist (and handles rollback of DOW-28 insertion) and it is needed in calculation. Calls proc that handles per 4 week (28 days) calculation of total minutes. Continues with existing per day iteration. Finally calls proc that adjusts end date based on holidays.

Proc B, per 4 weeks calculation (so you save at most 28 iterations per 4 weeks period). If DOW-28 will always be handled/created by Proc A then this proc is dependent on Proc A. Or... you can supply total_mins_28days via parameter so proc B won't have to visit WorkSchedule table and proc B is no longer dependent on proc A (can be reused)

Proc C, supplied with start date and tentative end date, returns adjusted end date after checking against holidays. Can be reused.

[Pradeep1210]

So that means I will have to keep some interval data in a separate table and use it as a helper table.
hmm... Looks like we are getting at it now. I've started getting ideas from leinad's and sz's ideas combined and trying to untangle things.

1. Create a table (named say WorkScheduleHelper). It will contain 3 columns - FromDate, ToDate, WorkMinutes.

2. Then I create a new stored procedure (named say BuildWorkSchedule) that will accept FromDate and ToDate to build the schedule and put the dates there. So this SP will first delete any dates between that range and recreate the schedule. So if I pass it 1-Jan-2010 to 1-Jan-2011, it will put 365 rows it in the table (1 per day). It will use the existing way of how I'm doing it right now i.e. spanning minute by minute. But now I do it only once in a while instead of earlier version of doing that for each schedule.

3. Then I initially create say one year schedule into it using this new SP and leave the rest to system administrators. I’ll give them a textbox and a button to create whatever schedules they want to. Though this won’t be absolutely required, it will help them if they want to generate data in advance for say next 5 years or so etc.

4. Now the work of my AddWorkMinutes function should be eased. It needs the work minutes from first and last day only and pick up the rest from my newly created WorkScheduleHelper table. That means this function will loop minute by minute for a max of 2 days. For the rest, it already knows how many work minutes from this table.

5. When updating the WorkSchedule table, if I find that one of the records numbered 1 to 7 is updated, I automatically recreate records in WorkScheduleHelper table from now onwards to whatever was the maximum date (don't delete anything, only update the WorkMinutes field); otherwise if one of the other rows is touched, I recreate schedule for that date range.

6. If AddWorkMinutes function fails to get specified number of minutes, I know that the required schedule is not present. I automatically generate schedule for next few days and retry until this function succeeds.

Do you think I'm on the right track? Or I missed something or will get stuck somewhere?