# Getting items with overlapping DateTime using Linq

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.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,
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.Closed.Value >= g.chat.Answered.Value && cr.Closed.Value <= g.chat.Closed.Value)
)
.Count ()
}
)


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

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.