How do I calculate the time difference from one row to another

Transact-SQL
1

Hi guys,

I need to create a new column and display the time difference between second and first row, third and second, fourth and third, and so on. This value will tell me the duration of the event.

Event_Duration
Event_Duration1306×717 79.6 KB

The results will be shown in the front end as show below:

event_duration01
event_duration011351×647 110 KB

Informix SQL:

SELECT agentstatedetail.agentid,agentstatedetail.eventdatetime,agentstatedetail.eventtype,agentstatedetail.reasoncode,resource.resourcename,resource.resourceloginid,resource.extension,reasoncodelabelmap.code,reasoncodelabelmap.label,reasoncodelabelmap.category,
CASE
WHEN eventtype = 1 AND code is NULL AND label is NULL THEN 'Logado'
-- Variation of reason codec with eventtype 2 - INICIO
WHEN eventtype = 2 AND code is NULL AND label is NULL THEN 'Nao Disponivel'
WHEN eventtype = 2 AND code = 2 AND label = 'Fim de Expediente' THEN 'Fim de Expediente'
WHEN eventtype = 2 AND code = 3 AND label = 'Descanso' THEN 'Descanso'
WHEN eventtype = 2 AND code = 4 AND label = 'Feedback' THEN 'Feedback'
WHEN eventtype = 2 AND code = 5 AND label = 'Treinamento' THEN 'Treinamento'
WHEN eventtype = 2 AND code = 6 AND label = 'Reuniao' THEN 'Reuniao'
WHEN eventtype = 2 AND code = 7 AND label = 'Refeicao' THEN 'Refeicao'
WHEN eventtype = 2 AND code = 8 AND label = 'Toalet' THEN 'Toalet'
WHEN eventtype = 2 AND code = 9 AND label = 'Backoffice' THEN 'BackOffice'
WHEN eventtype = 2 AND code = 10 AND label = 'Pos-Atendimento' THEN 'Pos-Atendimento'
WHEN eventtype = 2 AND code = 33 AND label = 'Supervisor Iniciado' THEN 'Status modificado supervisor'
WHEN eventtype = 2 AND code = 32742 AND label ='Nao DAC fora do gancho' THEN 'Nao DAC fora do gancho'
WHEN eventtype = 2 AND code = 32749 AND label ='Chamada Cancelada' THEN 'Chamada Cancelada'
WHEN eventtype = 2 AND code = 32750 AND label ='Ramal Modificado' THEN 'Ramal Modificado'
WHEN eventtype = 2 AND code = 32753 AND label ='Linha restrita' THEN 'Linha restrita'
WHEN eventtype = 2 AND code = 32754 AND label ='Dispositivo restrito' THEN 'Dispositivo restrito'
WHEN eventtype = 2 AND code = 32755 AND label ='Call Ended' THEN 'Chamada Finalizada'
WHEN eventtype = 2 AND code = 32756 AND label ='Telefone Funcionando' THEN 'Telefone Funcionando'
WHEN eventtype = 2 AND code = 32757 AND label ='CUCM Failover' THEN 'CUCM Failover'
WHEN eventtype = 2 AND code = 32758 AND label ='Wrap Up Timer Expiry' THEN 'Wrap Up Timer Expiry'
WHEN eventtype = 2 AND code = 32759 AND label ='Falha no telefone' THEN 'Telefone nao Funcinando'
WHEN eventtype = 2 AND code = 32760 AND label ='Agent Logon' THEN 'Agent Logon'
WHEN eventtype = 2 AND code = 32761 AND label ='Não DAC ocupado' THEN 'Nao DAC ocupado'
WHEN eventtype = 2 AND code = 32762 AND label ='Fora do gancho' THEN 'Fora do gancho'
WHEN eventtype = 2 AND code = 32763 AND label ='Chamada não respondida' THEN 'Chamada não respondida'
-- Variation of reason codec with eventtype 2 - FIM
-- Variation of reason codec with eventtype 3 - INICO
WHEN eventtype = 3 AND code is NULL AND label is NULL THEN 'Disponivel'
WHEN eventtype = 3 AND code = 33 AND label = 'Supervisor Iniciado' THEN 'Status modificado supervisor'
-- Variation of reason codec with eventtype 3 - FIM
WHEN eventtype = 4 AND code is NULL AND label is NULL THEN 'Reservado'
WHEN eventtype = 5 AND code is NULL AND label is NULL THEN 'Falando'
WHEN eventtype = 6 AND code is NULL AND label is NULL THEN 'Trabalho'
-- Variation of reason codec with eventtype 7 - INICIO
WHEN eventtype = 7 AND code = 1 AND label ='Logout' THEN 'Deslogado'
WHEN eventtype = 7 AND code = 22 AND label ='Supervisor Iniciado' THEN 'Supervisor Iniciado'
WHEN eventtype = 7 AND code = 255 AND label ='Falha na conexão' THEN 'Falha na conexao'
WHEN eventtype = 7 AND code = 32740 AND label ='Sistema inicializado Relogin' THEN 'Sistema inicializado Relogin'
WHEN eventtype = 7 AND code = 32741 AND label ='Conflito de ramal' THEN 'Conflito de ramal'
WHEN eventtype = 7 AND code = 32748 AND label ='Agente deletado' THEN 'Agente deletado'
WHEN eventtype = 7 AND code = 32764 AND label ='Sistema em espera' THEN 'Sistema em standby'
WHEN eventtype = 7 AND code = 32765 AND label ='Desconectar Sistema' THEN 'Perda de Conexao'
WHEN eventtype = 7 AND code = 32766 AND label ='Agente iniciado' THEN 'Finesse Fechado'
WHEN eventtype = 7 AND code = 32767 AND label ='Conflito de Dispositivo' THEN 'Conflito de Dispositivo'
-- Variation of reason codec with eventtype 7 - FIM
ELSE label
END motivo,
CASE
WHEN eventtype = 1 AND Reasoncode = 0 AND code is NULL AND category is NULL THEN 'Logado'
WHEN eventtype = 2 AND reasoncode = 0 AND code is NULL AND category is NULL THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 2 AND code = 2 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 3 AND code = 3 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 4 AND code = 4 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 5 AND code = 5 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 6 AND code = 6 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 7 AND code = 7 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 8 AND code = 8 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 9 AND code = 9 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 10 AND code = 10 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32742 AND code = 32742 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32749 AND code = 32749 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32750 AND code = 32750 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32753 AND code = 32753 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32754 AND code = 32754 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32755 AND code = 32755 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32756 AND code = 32756 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32757 AND code = 32757 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32758 AND code = 32758 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32759 AND code = 32759 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32760 AND code = 32760 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32761 AND code = 32761 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32762 AND code = 32762 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 2 AND reasoncode = 32763 AND code = 32763 AND category ='NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 3 AND reasoncode = 0 AND code is NULL AND category is NULL THEN 'Disponivel'
WHEN eventtype = 3 AND reasoncode = 33 AND code = 33 AND category = 'NOT_READY' THEN 'Nao Disponivel'
WHEN eventtype = 3 AND reasoncode = 33 AND code = 33 AND category = 'READY' THEN 'Disponivel'
WHEN eventtype = 4 AND reasoncode = 0 AND code is NULL AND category is NULL THEN 'Reservado'
WHEN eventtype = 5 AND reasoncode = 0 AND code is NULL AND category is NULL THEN 'Falando'
WHEN eventtype = 6 AND reasoncode = 0 AND code is NULL AND category is NULL THEN 'Trabalho'
WHEN eventtype = 7 AND reasoncode = 1 AND code = 1 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 22 AND code = 22 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 255 AND code = 255 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 32740 AND code = 32740 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 32741 AND code = 32741 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 32748 AND code = 32748 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 32764 AND code = 32764 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 32765 AND code = 32765 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 32766 AND code = 32766 AND category = 'LOGOUT' THEN 'Deslogado'
WHEN eventtype = 7 AND reasoncode = 32767 AND code = 32767 AND category = 'LOGOUT' THEN 'Deslogado'
ELSE NULL
END estado
FROM agentstatedetail
LEFT JOIN resource ON agentstatedetail.agentid = resource.resourceid
LEFT JOIN reasoncodelabelmap ON agentstatedetail.reasoncode = reasoncodelabelmap.code
ORDER BY agentstatedetail.agentid;

2

too much
too long code

put eventype reason code and anything else
into table or tables ( lookup tables )
and join

hope this helps :slight_smile: :slight_smile:

1 Like
3

Hi @harishgg1,

First of all. Thank you for answer my question.
I do not understand, I'm a newbie. Could you please give an example ?

I need to calculate the length of time the agent spent on the event. For example, If the agent used lunch break pause. How long was he on this break. I can only calculate this if I take the first line of the pause event and subtract from the next line that it changed its status to ready

4

Hi

The idea came to me by looking

Actually do it

Have to try

I will try

And

Let you know

:slightly_smiling_face::slightly_smiling_face::stuck_out_tongue_winking_eye::stuck_out_tongue_winking_eye:

1 Like
5

hi

please see the two temp tables i created for the case statements
just join and it will be just 2 lines code

temp tables created .... to replace huge case statement .. 
use tempdb 
go

drop table #case_ok 
go 

drop table #case_ok_1 
go 

create table #case_ok
(
eventype int ,
code int null,
label varchar(500) null,
result varchar(500) 
)
go 

create table #case_ok_1
(
eventype int ,
reasoncode int null,
code int null,
category varchar(500) null,
result varchar(500) 
)
go 

insert into #case_ok select 1, NULL, NULL, 'Logado'
insert into #case_ok select 2, NULL, NULL, 'Nao Disponivel'
insert into #case_ok select 2,2, 'Fim de Expediente' , 'Fim de Expediente'
insert into #case_ok select 2,3,'Descanso' , 'Descanso'
insert into #case_ok select 2,4 ,'Feedback' , 'Feedback'
insert into #case_ok select 2,5 ,'Treinamento' , 'Treinamento'
insert into #case_ok select 2,6, 'Reuniao' , 'Reuniao'
insert into #case_ok select 2,7,'Refeicao' , 'Refeicao'
insert into #case_ok select 2,8, 'Toalet' , 'Toalet'
insert into #case_ok select 2,9, 'Backoffice' , 'BackOffice'
insert into #case_ok select 2,10,'Pos-Atendimento' , 'Pos-Atendimento'
insert into #case_ok select 2,33,'Supervisor Iniciado' , 'Status modificado supervisor'
insert into #case_ok select 2,32742,'Nao DAC fora do gancho' , 'Nao DAC fora do gancho'
insert into #case_ok select 2,32749,'Chamada Cancelada' , 'Chamada Cancelada'
insert into #case_ok select 2,32750,'Ramal Modificado' , 'Ramal Modificado'
insert into #case_ok select 2,32753,'Linha restrita' , 'Linha restrita'
insert into #case_ok select 2,32754,'Dispositivo restrito' , 'Dispositivo restrito'
insert into #case_ok select 2,32755,'Call Ended' , 'Chamada Finalizada'
insert into #case_ok select 2,32756,'Telefone Funcionando' , 'Telefone Funcionando'
insert into #case_ok select 2,32757,'CUCM Failover' , 'CUCM Failover'
insert into #case_ok select 2,32758,'Wrap Up Timer Expiry' , 'Wrap Up Timer Expiry'
insert into #case_ok select 2,32759,'Falha no telefone' , 'Telefone nao Funcinando'
insert into #case_ok select 2,32760,'Agent Logon' , 'Agent Logon'
insert into #case_ok select 2,32761,'Não DAC ocupado' , 'Nao DAC ocupado'
insert into #case_ok select 2,32762,'Fora do gancho' , 'Fora do gancho'
insert into #case_ok select 2,32763,'Chamada não respondida' , 'Chamada não respondida'
insert into #case_ok select 3, NULL, NULL, 'Disponivel'
insert into #case_ok select 3,33,'Supervisor Iniciado' , 'Status modificado supervisor'
insert into #case_ok select 4, NULL, NULL, 'Reservado'
insert into #case_ok select 5, NULL, NULL, 'Falando'
insert into #case_ok select 6, NULL, NULL, 'Trabalho'
insert into #case_ok select 7,1,'Logout' , 'Deslogado'
insert into #case_ok select 7,22,'Supervisor Iniciado' , 'Supervisor Iniciado'
insert into #case_ok select 7,255,'Falha na conexão' , 'Falha na conexao'
insert into #case_ok select 7,32740,'Sistema inicializado Relogin' , 'Sistema inicializado Relogin'
insert into #case_ok select 7,32741,'Conflito de ramal' , 'Conflito de ramal'
insert into #case_ok select 7,32748,'Agente deletado' , 'Agente deletado'
insert into #case_ok select 7,32764,'Sistema em espera' , 'Sistema em standby'
insert into #case_ok select 7,32765,'Desconectar Sistema' , 'Perda de Conexao'
insert into #case_ok select 7,32766,'Agente iniciado' , 'Finesse Fechado'
insert into #case_ok select 7,32767,'Conflito de Dispositivo' , 'Conflito de Dispositivo'

--- this part  ??? ELSE label
-- reasoncode botom part code top part assumed same 

insert into #case_ok_1 select 1 , 0, NULL, NULL, 'Logado'
insert into #case_ok_1 select 2 , 0, NULL, NULL, 'Nao Disponivel'
insert into #case_ok_1 select 2 , 2,2 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 3,3 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 4,4 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 5,5 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 6,6 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 7,7 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 8,8 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 9,9 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 10,10 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32742,32742 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32749,32749 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32750,32750 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32753,32753 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32754,32754 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32755,32755 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32756,32756 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32757,32757 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32758,32758 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32759,32759 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32760,32760 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32761,32761 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32762,32762 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 2 , 32763,32763 ,'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 3 , 0, NULL, NULL, 'Disponivel'
insert into #case_ok_1 select 3 , 33,33 , 'NOT_READY' , 'Nao Disponivel'
insert into #case_ok_1 select 3 , 33,33 , 'READY' , 'Disponivel'
insert into #case_ok_1 select 4 , 0, NULL, NULL, 'Reservado'
insert into #case_ok_1 select 5 , 0, NULL, NULL, 'Falando'
insert into #case_ok_1 select 6 , 0, NULL, NULL, 'Trabalho'
insert into #case_ok_1 select 7 , 1,1 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 22,22 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 255,255 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 32740,32740 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 32741,32741 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 32748,32748 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 32764,32764 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 32765,32765 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 32766,32766 , 'LOGOUT' , 'Deslogado'
insert into #case_ok_1 select 7 , 32767,32767 , 'LOGOUT' , 'Deslogado'
-- this part ????ELSE NULL

DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' SELECT * FROM  ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql
1 Like
6

i will start working on this part ... soon

:slight_smile: :slight_smile: :+1::+1:

/*
I need to calculate the length of time the agent spent on the event. For example, If the agent used lunch break pause. How long was he on this break. I can only calculate this if I take the first line of the pause event and subtract from the next line that it changed its status to ready
*/

1 Like
7

what do you mean by time difference? hours, minutes, seconds?

use sqlteam
go


create table #feijoada(agentid int, 
eventname nvarchar(150), 
eventdatetime datetime);

insert into #feijoada
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:51.900' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:52.000' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:53.943' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:52.290' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:52.410' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:52.580' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:52.693' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2019-06-28 14:57:49.717' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2018-06-25 13:43:53.007' union
select 1, 'Daniel na cova do leão, por não fornecer dados de amostra',	'2019-02-22 10:37:31.340' 

;with cte as (
      select eventname, eventdatetime, row_number() over (order by eventdatetime asc) as seqnum
      from #feijoada
     )
select t.eventname,
       t.eventdatetime, 
       tprev.eventdatetime,
       DATEDIFF(SECOND, tprev.eventdatetime, t.eventdatetime) segundos, 
	   DATEDIFF(MINUTE, tprev.eventdatetime, t.eventdatetime) minutos, 
	   DATEDIFF(HOUR, tprev.eventdatetime, t.eventdatetime)  horas
from cte t left outer join
     cte tprev
     on t.seqnum = tprev.seqnum + 1;
drop table #feijoada
1 Like
8

It appears you are using Informix - and this forum is for SQL Server. You might get a better answer from an Informix related forum.

With that said - to get the previous/next rows values you can use LEAD/LAG windowing functions which are outlined here: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm

The basic idea is:

, Duration = datediff(second, lag(eventdatetime) over(partition by eventname order by eventdatetime), eventdatetime)

It will need to be expanded to accommodate null values and how you want to handle those.

1 Like
9

Hi @ jeffw8713,

I have a second database collecting and synch with informix db.

@ yosiasz
Yes HH:Mi:ss

10

Either way - you can use LEAD/LAG to get the previous/next rows value based on the specified partition and order. Using the sample data provided by @yosiasz:

 Select *
      , PreviousEventDate = lag(f.eventdatetime, 1, f.eventdatetime) over(Partition By f.agentid, f.eventname Order By f.eventdatetime)
      , DiffInSeconds = datediff(second, lag(f.eventdatetime, 1, f.eventdatetime) over(Partition By f.agentid, f.eventname Order By f.eventdatetime), f.eventdatetime)
   From #feijoada f

Adjust the partition as needed...

2 Likes
11

Is there a better way of doing this? to get HH:MM:ss

convert(varchar(8), 
				cast(DATEDIFF(HOUR, tprev.eventdatetime, t.eventdatetime) as varchar(10)) + ':' +
				cast(DATEDIFF(MINUTE, tprev.eventdatetime, t.eventdatetime) as varchar(10)) + ':' +
				cast(DATEDIFF(SECOND, tprev.eventdatetime, t.eventdatetime) as varchar(10)) , 108),
1 Like
12

I would use something like this:

      , cast(((datediff(second, tprev.eventdatetime, t.eventdatetime)) / 3600) As varchar(4)) + ' hour(s), '
      + cast((datediff(second, tprev.eventdatetime, t.eventdatetime) % 3600) / 60 As varchar(2)) + ' min, '
      + cast((datediff(second, tprev.eventdatetime, t.eventdatetime) % 60) As varchar(2)) + ' sec' As duration

If you want HH:MM:SS format - you also need to determine if you want leading zeroes - if so, then you need to add the leading zeroes and take the right xx number of characters to fill out the format:

      , right(concat('0000', cast(((datediff(second, tprev.eventdatetime, t.eventdatetime)) / 3600) As varchar(4)), 4) + ':'
      + right(concat('0', cast((datediff(second, tprev.eventdatetime, t.eventdatetime) % 3600) / 60 As varchar(2)), 2) + ':'
      + right(concat('0', cast((datediff(second, tprev.eventdatetime, t.eventdatetime) % 60) As varchar(2)), 2) As duration
2 Likes
13

on more ) in there :laughing: this should really be done on the presentation layer?

, right(concat('0000', cast(((datediff(second, tprev.eventdatetime, t.eventdatetime)) / 3600) As varchar(4))), 4) + ':'
      + right(concat('0', cast((datediff(second, tprev.eventdatetime, t.eventdatetime) % 3600) / 60 As varchar(2))), 2) + ':'
      + right(concat('0', cast((datediff(second, tprev.eventdatetime, t.eventdatetime) % 60) As varchar(2))), 2) As duration
1 Like
14

If it were me, I would:

Assuming an identity column, I'd add a second column of Identity+1, and join on that.

15

Calculate-the-time-difference-from-one-row-to-another with the help of this SQL Query:

SELECT

DATEDIFF(second, LAG(dataDate) OVER (ORDER BY dataDate), dataDate)

FROM rows

WHERE LAG(dataDate) OVER (ORDER BY dataDate) IS NOT NULL

16

What if there was a failed insert between the events? Or - different events logged during each time frame?

1 Like