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

I don't have a PC to verify syntax or anything, but I think this should give you a starting place:

WITH ChatWithRownum AS (    SELECT ChatID, User, LogID, CreatedOn, ROW_NUMBER() OVER(ORDER BY ChatID, CreatedOn) AS rownum    FROM ChatLog)SELECT First.ChatID, Second.User,    AVG(DATEDIFF(seconds, First.CreatedOn, Second.CreatedOn)) AS AvgElapsedTimeFROM ChatWithRownum First    JOIN ChatWithRownum Second ON First.ChatID = Second.ChatID        AND First.rownum = Second.rownum - 1WHERE First.User != Second.UserGROUP BY First.ChatID, Second.User

Essentially, the idea is to add row numbers to the data so you can join one row to the next row (so you have a statement followed by its immediate response). Once you have the rows joined, you can get the time elapsed between the two entries, and then group the data by the ChatID (I'm assuming that times between separate chats aren't relevant) and the two users. Like I said though, this is just a starting place as I'm sure there may be some additional criteria and/or bugs in my query :)

Try something simple like the following before moving into cursors.

select ChatId, User, datediff('second', min(CreatedOn, max(CreatedOn))/count(*)from ChatLoggroup by ChatId, User

This one works and doesn't involve using cursors. If I had more time, I could probably even eliminate the temp table, but hey... it works.

declare @operator varchar(50)set @operator = 'john'declare @customer varchar(50)set @customer = 'susan'declare @chatid intset @chatid = 1declare @t table (chatid int, username varchar(50), responsetime int)insert @t (chatid, username, responsetime)select ChatId,     Username,    datediff(second,     CreatedOn,    (        select min(createdon)        from chatlog        where createdon > cl.createdon        and username = @customer              and chatid = @chatid    ))from ChatLog clwhere chatid = @chatid and username = @operatorinsert @t (chatid, username, responsetime)select ChatId,     Username,     datediff(second,     CreatedOn,    (        select min(createdon)        from chatlog        where createdon > cl.createdon        and username = @operator              and chatid = @chatid    ))from ChatLog clwhere chatid = @chatid and username = @customerselect chatid, username, avg(responsetime) as avgresponsetime from @tgroup by chatid, usernameorder by username

Seems like you need a cursor to step through each line and check for the change of user in the record, and get the difference of that time, and store it somewhere(temp table maybe), and aggregate it later.

I believe it can be done in TSQL, logic would be something like:

DECLARE delta CURSOR FORSELECT user, createdon from tableorder by createdon --or logidOPEN deltafetch next from delta into @username, @timewhile @@fetch_status = 0beginFETCH PRIOR FROM delta into @username_prior, @time_priorIF @username_prior  @usernameBEGIN  @timedelta = @time - @time_prior  @total = @total + @timedelta  insert into #average (@username, @total)END fetch next from delta into @username, @timeENDCLOSE deltaDEALLOCATE deltaSELECT user, AVG(time) from #averagegroup by user

I'm sure you can figure out how to declare all the parameters.