[RESOLVED] Workday UDF Calculator

[ixalmida]

Hi. I am trying to create a user-defined function in MySQL to calculate the expiration date based on a start date and a variable number of hours. The rub is that I need it to skip non-working hours. The function definition would look something like this:

CREATE FUNCTION xHoursToBusTime (hrs INT, fromDt DATETIME) RETURNS DATETIME

So, for example, if hrs = 15 and fromDt = '01/24/2010 16:05:00', the function would need to decide that the next working hour is '01/25/2010 08:00:00', subtract the time until 5pm (or 9 hours), leaving hrs = 6, then increment the day and add the remaining hours to fromDt. This would return the result, '01/26/2010 14:05:00'.

I've read that you can't have recursive functions so I'm not sure how to go about this. Plus, it will need to check a holiday table somewhere in the function and increment the day if there is a match. The whole thing gets kinda sticky!

Does anyone have any ideas? I'll settle for some pseudocode or anything that might help me solve this one.

[johanafm]

Since you are dealing with fixed schedules, there is need for neither iteration nor recursion to solve the first problem.

You have the starting time each day, 8. You have the day length of 9 hours. So, floor(hrs / 9) is the number of days to add. hrs % 9 is the number of hours into the last day. All that remains is to check if fromDt should move the time ahead by one additional day.

Concerning holidays, you should do the above first to get the total number of days.
set a start limit for checking holiday days, initially = the first day of the period (fromDt, or the day after fromDt if it was after office hours). numdays = total number of days

while (retrieve the number of holiday days from start to start + numdays)
start = previous end date.
add number of holiday days to total number of days
numdays = holiday days.
end while

[ixalmida]

Thanks - great solution! That's exactly what I needed.

[ixalmida]

Here's my completed function, and it functions nicely. It is a bit less graceful than I'd like, but I'll deal. A possible failure point is if there are more than two holidays in a row, but that shouldn't happen.

CREATE DEFINER=`ixalmida`@`%` FUNCTION `xHrsToDate`(hrs INT, stDate DATETIME) RETURNS datetime
    DETERMINISTIC
BEGIN
  DECLARE numWeeks, numDays, numHours, numMins, dow, tod INT;
  DECLARE retTime DATETIME;

  SET numDays = FLOOR(hrs/9);
  SET numHours = hrs % 9;
  SET numMins = MINUTE(stDate);
  SET dow = DAYOFWEEK(stDate);
  SET tod = HOUR(stDate);
  SET numWeeks = 0;

  /* Convert numDays into weeks if 5 or more */
  IF numDays > 4 THEN
    SET numWeeks = FLOOR(numDays/5);
    SET numDays = numDays % 5;
  END IF;

  /* If start time is past 5pm, reset to 8am and increment day */
  IF tod > 17 THEN
    SET stDate = FROM_UNIXTIME(UNIX_TIMESTAMP(DATE(stDate)) + 28800);
    SET stDate = stDate + INTERVAL 1 DAY;
    SET dow = DAYOFWEEK(stDate);
    SET tod = HOUR(stDate);
  END IF;

  /* If start time is before 8am, reset to 8am */
  IF tod < 8 THEN
    SET stDate = FROM_UNIXTIME(UNIX_TIMESTAMP(DATE(stDate)) + 28800);
    SET tod = HOUR(stDate);
  END IF;

  /* If starting time + numHours > 4pm, get remaining hours, and set start time to 8am + 1 day */
  IF tod + numHours > 16 THEN
    SET numHours = numHours - (17 - tod);
    SET stDate = FROM_UNIXTIME(UNIX_TIMESTAMP(DATE(stDate)) + 28800);
    SET stDate = stDate + INTERVAL 1 DAY;
    SET dow = DAYOFWEEK(stDate);
  END IF;

  /* If updated start day is Sunday, add a day */
  IF dow = 1 THEN
    SET stDate = stDate + INTERVAL 1 DAY;
  END IF;

  /* If updated start day is Saturday, add 2 days */
  IF dow = 2 THEN
    SET stDate = stDate + INTERVAL 2 DAY;
  END IF;

  /* Should be able to calculate end date now and fall within working hours */
  SET retTime = stDate + INTERVAL numWeeks WEEK + INTERVAL numDays Day + INTERVAL numHours HOUR + INTERVAL numMins MINUTE;

  /* Make sure we don't land on a holiday */
  IF (SELECT 1 FROM sysHolidays WHERE DATE(holidate) = DATE(retTime)) = 1 THEN
    SET retTime = retTime + INTERVAL 1 DAY;
    SET dow = DAYOFWEEK(retTime);
    /* Make sure the new day isn't during the weekend */
    IF dow = 1 THEN
      SET retTime = retTime + INTERVAL 1 DAY;
    END IF;
    IF dow = 7 THEN
      SET retTime = retTime + INTERVAL 2 DAY;
    END IF;
  END IF;

  /* Repeat the holiday check (for Thanksgiving and Christmas, since there are two days in a row) */
  IF (SELECT 1 FROM sysHolidays WHERE DATE(holidate) = DATE(retTime)) = 1 THEN
    SET retTime = retTime + INTERVAL 1 DAY;
    SET dow = DAYOFWEEK(retTime);
    /* Make sure the new day isn't during the weekend */
    IF dow = 1 THEN
      SET retTime = retTime + INTERVAL 1 DAY;
    END IF;
    IF dow = 7 THEN
      SET retTime = retTime + INTERVAL 2 DAY;
    END IF;
  END IF;

  return retTime;
END