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 =&gt; (cr.Created.AddHours (10).Date == DateTime.Now.AddDays (-1).Date))
.Where (cr =&gt; cr.OperatorID.HasValue)
.Join (
    Operators, 
    chat =&gt; chat.OperatorID.Value, 
    op =&gt; op.ID, 
    (chat, op) =&gt; 
        new  
        {
            chat = chat, 
            op = op
        }
)
.Select (
    g =&gt; 
        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 =&gt; (cr.OperatorID == g.chat.OperatorID))
                        .Where (cr =&gt; (cr.ID != g.chat.ID))
                        .Where (
                           cr =&gt;    (cr.Answered.Value &gt;= g.chat.Answered.Value &amp;&amp; cr.Answered.Value &lt;= g.chat.Closed.Value) 
                                     || (cr.Closed.Value &gt;= g.chat.Answered.Value &amp;&amp; cr.Closed.Value &lt;= g.chat.Closed.Value)
                                     || (cr.Answered.Value &lt; g.chat.Answered.Value &amp;&amp; cr.Closed.Value &gt; g.chat.Closed.Value)                        
            )
            .Count ()
        }
)
A faster version of your final where clause is:

cr.Answered.Value &lt;= g.chat.Closed.Value &amp;&amp; cr.Closed.Value &gt;= 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 &lt; g.chat.Closed.Value &amp;&amp; cr.Closed.Value &gt; g.chat.Answered.Value


You can also remove the .Where (cr =&gt; (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.