All geek questions in one place

Calculating dates around business days / hours?

I am currently working on a project tracking site. You can create service level agreements (SLAs) in it. They are configured with the days of the week when the project can be processed, as well as the time on each of these days. For instance. on Monday it can be between 08:00 and 16:00, and then on Friday from 10:00 to 14:00. They are also configured with a time delay based on priority. For instance. A project created with a Low priority has a two-week deadline, and a High priority project has a four-hour deadline.

The problem I am facing is calculating the deadline AROUND the hours described earlier. Let's say I create a project on Monday at 2 p.m. with the priority "High". This means that I have four hours for this project. But due to work time, I have two hours on Monday (until 4:00 p.m.), and then another two hours on Friday. This means that the deadline must be set on Friday at 12:00.

I spent quite a lot of time on this, and I can find many examples of how many working hours there are between the end date of the end of the beginning. I just can't figure out how to convert it to FINDING ending a datetime, given the start time and the amount of time before the deadline.

Day / time areas are stored in the sql database in the format:

Day (e.g. 1 on Monday) StartHour EndHour

StartHour / EndHour are stored as DateTimes, but, of course, only the time part is important.

The way I understand this, I need to somehow sort through these times and do some datetime calculations. I just can't figure out what these calculations should be, what is the best way.

I found this question here on the site when I wrote this. This is what I want, and I'm playing with him right now, but I'm still losing exactly how to get him working around my dynamic work days / hours.

+4
source share
4 answers

Here is some C # code that can help, it can be much cleaner, but this is a quick first draft.

class Program { static void Main(string[] args) { // Test DateTime deadline = DeadlineManager.CalculateDeadline(DateTime.Now, new TimeSpan(4, 0, 0)); Console.WriteLine(deadline); Console.ReadLine(); } } static class DeadlineManager { public static DateTime CalculateDeadline(DateTime start, TimeSpan workhours) { DateTime current = new DateTime(start.Year, start.Month, start.Day, start.Hour, start.Minute, 0); while(workhours.TotalMinutes > 0) { DayOfWeek dayOfWeek = current.DayOfWeek; Workday workday = Workday.GetWorkday(dayOfWeek); if(workday == null) { DayOfWeek original = dayOfWeek; while (workday == null) { current = current.AddDays(1); dayOfWeek = current.DayOfWeek; workday = Workday.GetWorkday(dayOfWeek); if (dayOfWeek == original) { throw new InvalidOperationException("no work days"); } } current = current.AddHours(workday.startTime.Hour - current.Hour); current = current.AddMinutes(workday.startTime.Minute - current.Minute); } TimeSpan worked = Workday.WorkHours(workday, current); if (workhours > worked) { workhours = workhours - worked; // Add one day and reset hour/minutes current = current.Add(new TimeSpan(1, current.Hour * -1, current.Minute * -1, 0)); } else { current.Add(workhours); return current; } } return DateTime.MinValue; } } class Workday { private static readonly Dictionary<DayOfWeek, Workday> Workdays = new Dictionary<DayOfWeek, Workday>(7); static Workday() { Workdays.Add(DayOfWeek.Monday, new Workday(DayOfWeek.Monday, new DateTime(1, 1, 1, 10, 0, 0), new DateTime(1, 1, 1, 16, 0, 0))); Workdays.Add(DayOfWeek.Tuesday, new Workday(DayOfWeek.Tuesday, new DateTime(1, 1, 1, 10, 0, 0), new DateTime(1, 1, 1, 16, 0, 0))); Workdays.Add(DayOfWeek.Wednesday, new Workday(DayOfWeek.Wednesday, new DateTime(1, 1, 1, 10, 0, 0), new DateTime(1, 1, 1, 16, 0, 0))); Workdays.Add(DayOfWeek.Thursday, new Workday(DayOfWeek.Thursday, new DateTime(1, 1, 1, 10, 0, 0), new DateTime(1, 1, 1, 16, 0, 0))); Workdays.Add(DayOfWeek.Friday, new Workday(DayOfWeek.Friday, new DateTime(1, 1, 1, 10, 0, 0), new DateTime(1, 1, 1, 14, 0, 0))); } public static Workday GetWorkday(DayOfWeek dayofWeek) { if (Workdays.ContainsKey(dayofWeek)) { return Workdays[dayofWeek]; } else return null; } public static TimeSpan WorkHours(Workday workday, DateTime time) { DateTime sTime = new DateTime(time.Year, time.Month, time.Day, workday.startTime.Hour, workday.startTime.Millisecond, workday.startTime.Second); DateTime eTime = new DateTime(time.Year, time.Month, time.Day, workday.endTime.Hour, workday.endTime.Millisecond, workday.endTime.Second); if (sTime < time) { sTime = time; } TimeSpan span = eTime - sTime; return span; } public static DayOfWeek GetNextWeekday(DayOfWeek dayOfWeek) { int i = (dayOfWeek == DayOfWeek.Saturday) ? 0 : ((int)dayOfWeek) + 1; return (DayOfWeek)i; } private Workday(DayOfWeek dayOfWeek, DateTime start, DateTime end) { this.dayOfWeek = dayOfWeek; this.startTime = start; this.endTime = end; } public DayOfWeek dayOfWeek; public DateTime startTime; public DateTime endTime; }
+2
source

There is a recursive solution that might work, try to think along these lines:

 public DateTime getDeadline(SubmitTime, ProjectTimeAllowed) { if (SubmitTime+ProjectTimeAllowed >= DayEndTime) return getDeadline(NextDayStart, ProjectTimeAllowed-DayEndTime-SubmitTime) else return SubmitTime + ProjectTimeAllowed }

Obviously, this is a pretty crude pseudo code. Hope this just gives you another way to think about the problem.

+1
source

This is how I do it. The algorithm should check if the problem can be closed today, and if not, use all of today's time to reduce the remaining time and move on to tomorrow.

  • Find the time when you should close the problem as TimeSpan (I call this the remaining time of the problem)
  • For each business day, create a DateTime that has only a start and end time.
  • Set the start time.
  • Loop:
    • Find the remaining time by subtracting the end time today minus the start time (the result should be TimeSpan)
    • If today's remaining time is longer than the remaining time, take today's date and today is the start time + remaining time
    • If the remaining time of the problem is longer, set the remaining time of the problem as the remaining time of the problem minus today's remaining time, go to tomorrow and go to the beginning of the cycle.
+1
source

Using Stu answer as a starting point, change the IsInBusinessHours function to find the working hours for the date parameter. You can use the following procedure:

 CREATE PROCEDURE [dbo].[IsInBusinessHours] @MyDate DateTime AS BEGIN SELECT CASE Count(*) WHEN 0 THEN 0 ELSE 1 END AS IsBusinessHour FROM WorkHours WHERE (DATEPART(hour, StartHours) <= DATEPART(hour, @MyDate)) AND (DATEPART(hour, EndHours) > DATEPART(hour, @MyDate)) AND (Day = DATEPART(WEEKDAY, @MyDate)) END
+1
source

Source: https://habr.com/ru/post/1277655/

More articles:


All Articles