How do I average the difference between specific values in TSQL?

Posted by jvenema on Stack Overflow See other posts from Stack Overflow or by jvenema
Published on 2011-01-04T21:10:57Z Indexed on 2011/01/04 21:53 UTC
Read the original article Hit count: 162

Filed under:
|
|

Hey folks, sorry this is a bit of a longer question...

I have a table with the following columns:

[ChatID] [User] [LogID] [CreatedOn] [Text]

What I need to find is the average response time for a given user id, to another specific user id. So, if my data looks like:

[1] [john] [20] [1/1/11 3:00:00] [Hello]
[1] [john] [21] [1/1/11 3:00:23] [Anyone there?]
[1] [susan] [22] [1/1/11 3:00:43] [Hello!]
[1] [susan] [23] [1/1/11 3:00:53] [What's up?]
[1] [john] [24] [1/1/11 3:01:02] [Not much]
[1] [susan] [25] [1/1/11 3:01:08] [Cool]

...then I need to see that Susan has an average response time of (20 + 6) / 2 => 13 seconds to John, and John has an average of (9 / 1) => 9 seconds to Susan.

I'm not even sure this can be done in set-based logic, but if anyone has any ideas, they'd be much appreciated!

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server