How do I average the difference between specific values in TSQL?
- by jvenema
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!
