Business Hours of Operation in SQL/C#

Question

I am having a massive brain fart on this one for some reason. I have a table with "Hours of Operation" for a business. The table has: - Day (Monday = 1, Sunday = 7) - Start Time - End Time

I'm trying to figure out how to write the cleanest code (using Linq to SQL) that will check if hours are overlapping on submit of new hours and modify all records accordingly. For example, if the database already has these records: - Start = 8am, End = Noon - Start = 2pm, End = 5pm

And someone adds: - Start = Noon, End = 6pm

I want the code to figure out how to combine those 3 records and leave me with one record. But I also need to be able to only combine when necessary so if someone were to insert this instead: - Start = Noon, End = 1pm

Then I would end up with 2 records (the first would be modified).

Like I said...I just can't seem to wrap my head around this. Help?

Answer 1

I'd suggest you make a little change in your time interval representation, to have a Length property instead of a EndTime one.

class TimeInterval
{
    public int Day { set; get; }
    public DateTime StartTime { set; get; }
    public int Length { set; get; }
}

As we are interested in finding overlapping intervals, the next methods will be handy, I would place them inside TimeInterval class.

public int StartMinuteInWeek()
{
    return Day * 24 * 60 + StartTime.Hour * 60 + StartTime.Minute;
}

public int EndMinuteInWeek()
{
    return StartMinuteInWeek() + Length;
}

Then you just need to find any TimeInterval that overlaps your new time interval. You have to be aware that the new interval can extend any existing one, or even join two separate intervals, or just be contained by an already defined interval.

To get the record that overlaps on the start of the interval, use the following query:

var startOverlap = (from rec in intervals
    where rec.StartMinuteInWeek() <= timeInterval.StartMinuteInWeek() && rec.EndMinuteInWeek() >= timeInterval.StartMinuteInWeek()
    select rec).FirstOrDefault();

timeInterval is the new interval you're trying to add.

for the end overlap use the following one:

var endOverlap = (from rec in intervals
    where rec.StartMinuteInWeek() <= timeInterval.EndMinuteInWeek() && rec.EndMinuteInWeek() >= timeInterval.EndMinuteInWeek()
    select rec).FirstOrDefault();

If startOverlap is null, then there is no overlap in the start of the interval. The same for endOverlap. If startOverlap == endOverlap, then the new interval is fully containd inside an already existing interval.

I've defined StartTime as a DateTime type, though I'm only interested in the time portion, you can change this if you want to two fields named Hour and Minute, or perhaps one only field that represents the starting minute in the week of the interval.

EDIT

As I mentioned in my comment, I've realized that a new time interval can overlap several already defined intervals. For example, if normally the operation goes from 8:00 to 20:00 and stops at lunch time, and now a new time interval is defined that goes trough all the week 24x7, then the new time interval will contain 14 intervals.

A safe way to go through this, is first to detect all those overlapped intervals, and then adjust the new interval to make sure it fully contains any overlapped interval, and finally delete all overlaps.

To find out if the new interval overlaps an old one, I'm going to define this handy method in TimeInterval class.

public bool IsInsideInterval(int minuteInWeek)
{
    return minuteInWeek >= StartMinuteInWeek() &&
        minuteInWeek <= EndMinuteInWeek();
}

Now I'm finding out all the intervals that it's start minute or end minute is inside the new interval.

var overlaps = (from rec in intervals where
    newInterval.IsInsideInterval(rec.StartMinuteInWeek()) ||
    newInterval.IsInsideInterval(rec.EndMinuteInWeek())
    select rec).ToList();

And now you've got to make sure that the new interval limits are OK.

foreach (TimeInterval rec in overlaps)
{
    if (rec.StartMinuteInWeek() < newInterval.StartMinuteInWeek())
    {
        newInterval.Day = rec.Day;
        newInterval.StartTime = rec.StartTime;
    }
    if (rec.EndMinuteInWeek() > newInterval.EndMinuteInWeek())
    {
        // You have to calc the Length being careful to not count twice the minutes that overlaps.
        newInterval.Length = newInterval.Length + rec.Length - (newInterval.EndMinuteInWeek() - rec.StartMinuteInWeek());
    }
}

Finally you can delete all intervals in overlaps and then insert newInterval