Getting items with overlapping DateTime using Linq

Question

I have a live chat database and I am trying to get an idea of how many concurrent chats staff are taking. I'm not sure of the best way to report it so I have decided to get a list of chats for a given period then for each one display a count of chats that overlapped that one in some way.

I am querying 2 tables across a join but this is just to get the Operators name. The fields that have the relevant dates are:

DateTime? ChatRequests.Answered
DateTime? ChatRequests.Closed

I think I have everything covered. I just want to see if anyone can tell me if I'm missing anything, or if there is a better approach to it.

ChatRequests
.Where (cr => (cr.Created.AddHours (10).Date == DateTime.Now.AddDays (-1).Date))
.Where (cr => cr.OperatorID.HasValue)
.Join (
 Operators, 
 chat => chat.OperatorID.Value, 
 op => op.ID, 
 (chat, op) => 
 new 
 {
 chat = chat, 
 op = op
 }
)
.Select (
 g => 
 new 
 {
 Operator = g.op.FullName, 
 Answered = g.chat.Answered.Value.AddHours (10), 
 Closed = g.chat.Closed.Value.AddHours (10), 
 Duration = (Decimal)((g.chat.Closed.Value - g.chat.Answered.Value).TotalMinutes), 
 Company = g.chat.AccountName, 
 Contact = g.chat.ContactName, 
 OtherChatsInSamePeriod = ChatRequests
 .Where (cr => (cr.OperatorID == g.chat.OperatorID))
 .Where (cr => (cr.ID != g.chat.ID))
 .Where (
 cr => (cr.Answered.Value >= g.chat.Answered.Value && cr.Answered.Value <= g.chat.Closed.Value) 
 || (cr.Closed.Value >= g.chat.Answered.Value && cr.Closed.Value <= g.chat.Closed.Value)
 || (cr.Answered.Value < g.chat.Answered.Value && cr.Closed.Value > g.chat.Closed.Value) 
 )
 .Count ()
 }
)

Answer 1

A faster version of your final where clause is:

cr.Answered.Value <= g.chat.Closed.Value && cr.Closed.Value >= g.chat.Answered.Value

If you do not want to count end points as overlapping (e.g. a call ending exactly at 9:59 and a call beginning exactly at 9:59 do not overlap) then use:

cr.Answered.Value < g.chat.Closed.Value && cr.Closed.Value > g.chat.Answered.Value

You can also remove the .Where (cr => (cr.ID != g.chat.ID)) condition and subtract 1 from the total count since it should be guaranteed that there is only one call taking place by the same operator during the same time period in question.