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.