find common DateTime from list of DateTime, if no common then find most common

Question

I want to find common DateTime from available list of DateTime in my database.

Background

Lets suppose,

I want to meet some peoples, and I say I want to meet certain peoples between Datetime X(2014-02-16 09:00:00.000) to DateTime Y(2014-02-26 05:00:00.000).

Then the peoples i want to meet with will reply saying i will be available in following dates: Date1(Certain date from certain start time to certain end time), Date2(certain date from certain time to certain time),...and so on.

Lets consider these are the responses

Attendee1(Some GuidId):

Response1: Start Time=2014-02-23 09:00 AM, EndTime = 2014-02-17 11:00 AM,

Response2 : Start Time=2014-02-24 10:00 AM, EndTime = 2014-02-17 12:00 PM,

Response3 : Start Time=2014-02-25 10:00 AM, EndTime = 2014-02-17 11:00 AM,

Response4 : Start Time=2014-02-23 01:00 PM, EndTime = 2014-02-17 5:00 PM

Attendee2(Some GuidId):

Response1: Start Time=2014-02-22 09:00 AM, EndTime = 2014-02-17 05:00 PM,

Response2 : Start Time=2014-02-23 09:00 AM, EndTime = 2014-02-17 05:00 PM,

Response3 : Start Time=2014-02-25 09:00 AM, EndTime = 2014-02-17 12:00 PM,

Attendee3(Some GuidId):

Response1: Start Time=2014-02-22 11:00 AM, EndTime = 2014-02-17 02:00 PM,

Response2 : Start Time=2014-02-23 04:00 PM, EndTime = 2014-02-17 03:00 PM,

Response3 : Start Time=2014-02-23 04:00 PM, EndTime = 2014-02-17 05:00 PM,

Response4 : Start Time=2014-02-24 02:00 AM, EndTime = 2014-02-17 05:00 PM,

Response5 : Start Time=2014-02-25 11:00 AM, EndTime = 2014-02-17 12:00 PM,

Here, in above scenario, system should provide matching dates as:

2014-02-23 04:00 PM to 2014-02-23 05:00 PM 

and

2014-02-25 11:00 AM to 2014-02-25 12:00 PM

So once every one has replied. I would like to find the common DateTime or common List<DateTime>(if exists).

Or find the DateTime(List) that has highest number of matches.

I know I have to show what I have tried and what I have done, but I have no idea in my mind how should I start.

Any kind of suggestions or hints would be appreciated.

Edit 1:

So in database there is table called Appointment which stores the StartDateTime and EndDateTime

public class Appointment
{

    [Key]
    public Guid Id { get; set; }

    public virtual ICollection<Attendee> Attendees { get; set; }

    public DateTime StartDateTime { get; set; }
    public DateTime EndDateTime { get; set; }      

}

and between these dates people(Attendee) attending will give their response. Each person(who will respond), their information is stored in database table called Attendees

public class Attendee
{
    public Guid AttendeeId { get; set; }
    public virtual ICollection<Response> Responses { get; set; } 
}

And for each user their response is stored in Responses table whose model would look like

public class Response
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public Guid AttendeeId { get; set; }
    public DateTime StartDateTime { get; set; }
    public DateTime EndDateTime { get; set; }

}

PS: It is a MVC application and database is created using code first approach.

Also, would it make sense to add AppointmentId to Response table? or would that be bad approach? But I think it would make the querying easier.

Answer 1

An approach that might work:

• Calculate a List of the combinations that everyone can attend.
• You would start with all responses for the first Attendee, and then enumerate through the other attendees.
• As you enumerate through, for each potential time that can still work, narrow the time window to fit all of the times that all possible attendees can use

Might need some touching up to handle edge cases, but the following code should get you on the right path:

public class MeetingTime 
{
  DateTime Start { get; set; }
  DateTime End { get; set; }
  List<Response> Responses { get; set; }
  public int NumAttendees 
  {        
    get { return Responses.Select(x => x.AttendeeId).Distinct().Count(); }
  }

  public MeetingTime(Response response) 
  {
    Start = response.StartDateTime;
    End = response.EndDateTime;
    Responses = new List<Response>();
  }

  public void MergeMeetingTime(Response response) 
  {
    // if response times are within current times, can be merged
    if (Start <= response.StartDateTime && response.EndDateTime <= End)
    {
       Start = response.StartDateTime;
       End = response.EndDateTime;
       Responses.Add(response);
    }
  }
}

public List<MeetingTime> FindMeetingTimes() 
{
  var attendees = GetAttendees();
  var times = new List<MeetingTime> 
  bool isFirstAttendee = true;
  foreach (Attendee attendee in attendees) 
  {
    if (isFirstAttendee) 
    {
      foreach (Response response in attendee.Responses)
      {
        times.Add(new MeetingTime(response));
      }
      isFirstAttendee = false;
    } 
    else 
    {
      // Go through attendee.Responses and compare each with the current times
      // for each one, if it overlaps with a MeetingTime, then adjust 
      // the Start/End dates accordingly and increment Count
      // Uses same approach as above.          
      foreach (Response response in attendee.Responses)
      {
        times.ForEach(x => x.MergeMeetingTime(response));
      }
    }
  }
  // Remove all times where not everyone can attend
  times.Remove(x => x.NumAttendees < attendees.Count()).ToList();
  return times;
}

At the end, you remove all times where not everyone can attend. The times that are remaining should each have Start/End DateTimes that all Attendees can make.