5
\$\begingroup\$

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 ()
        }
)
\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.