How to get the difference in seconds with Sql Server

Navigation
0

I know that to get the difference of seconds between Start and End is like this: Case 1: 

DECLARE @TB_DIFERENCA_INICIO_FIM as table (
    ID  int,
    DataInicio datetime,
    DataFim datetime
)

INSERT INTO @TB_DIFERENCA_INICIO_FIM VALUES
(1,'2018-10-19 18:18:30.240','2018-10-19 18:18:32.240'),
(2,'2018-10-19 18:18:32.050','2018-10-19 18:18:33.050')

select DATEDIFF(second, DataInicio, DataFim) AS IntervaloEmSegundos from @TB_DIFERENCA_INICIO_FIM

Case 2:

Doubt is for this case. Home I need to know the difference between the 8 records:
I explain:
What is the difference between Registry 1 and Registry 2; Home What is the difference between Record 2 and Record 3, and so on: 

DECLARE @TB_HORA as table (
    ID  int,
    DataExecucao datetime
)

INSERT INTO @TB_HORA VALUES
(1,'2018-10-19 18:18:30.240'),
(2,'2018-10-19 18:18:29.050'),
(3,'2018-10-19 18:18:29.240'),
(4,'2018-10-19 18:18:28.240'),
(5,'2018-10-19 18:18:27.560'),
(6,'2018-10-19 18:18:25.240'),
(7,'2018-10-19 18:18:24.050'),
(8,'2018-10-19 18:18:24.240')

select * from @TB_HORA

Case 3:
For Case 2 the solution to Motta works perfectly, but after analyzing the actual situation of the Database that is as follows: 

DECLARE @TB_HORA as table (
    ID  int,
    ID_Servico int,
    DataExecucao datetime
)

INSERT INTO @TB_HORA VALUES
( 1,49,'2018-10-19 18:18:30.313'),
( 2, 1,'2018-10-19 18:18:31.957'),
( 3,33,'2018-10-19 18:18:35.240'),
( 4,49,'2018-10-19 18:18:44.050'),
( 5,53,'2018-10-19 18:18:55.240'),
( 6,53,'2018-10-19 18:18:55.713'),
( 7,49,'2018-10-19 18:18:58.240'),
( 8,51,'2018-10-19 18:18:59.240'),
( 9,54,'2018-10-19 18:19:00.240'),
(10,33,'2018-10-19 18:19:01.240'),
(11,53,'2018-10-19 18:19:05.240')

select DATEDIFF(second, TB1.DataExecucao , TB2.DataExecucao) as intervalo
    from @TB_HORA as TB1
        inner join @TB_HORA as TB2
            on TB1.ID_Servico = TB2.ID_Servico
where TB1.ID_Servico = 49

As you can see for Case 3 the way it is does not work. Home I need to return the difference in seconds, for example: only between ID_Servico = 49 registers.

    
asked by anonymous 19.10.2018 / 23:35

2 answers

-1

I did so: 

drop table #TB_OK

DECLARE @TB_HORA as table (
    ID  int,
    ID_Servico int,
    DataExecucao datetime
)

INSERT INTO @TB_HORA VALUES
( 1,49,'2018-10-19 18:18:30.313'),
( 2, 1,'2018-10-19 18:18:31.957'),
( 3,33,'2018-10-19 18:18:35.240'),
( 4,49,'2018-10-19 18:18:44.050'),
( 5,53,'2018-10-19 18:18:55.240'),
( 6,53,'2018-10-19 18:18:55.713'),
( 7,49,'2018-10-19 18:18:55.240'),
( 8,51,'2018-10-19 18:18:59.240'),
( 9,54,'2018-10-19 18:19:00.240'),
(10,49,'2018-10-19 18:19:01.240'),
(11,53,'2018-10-19 18:19:05.240')

SELECT 
  ROW_NUMBER() OVER(ORDER BY DataExecucao ASC) AS Id,
  ID_Servico, DataExecucao
INTO #TB_OK
FROM @TB_HORA 
WHERE ID_Servico = 49

select DATEDIFF(second, TB1.DataExecucao , TB2.DataExecucao) as Segundos
    from #TB_OK as TB1
        inner join #TB_OK as TB2
            on TB1.Id = TB2.Id-1

Credits to Motta

    
20.10.2018 / 19:53
0

Here is a solution for SQL Server 2012 (or later). 

-- código #1
SELECT ID_Serviço, ID, 
       lag (DataExecucao) over (partition by ID_Servico order by ID) as DataExecucao_ant,
       DataExecucao,
       datediff (second, 
                 lag (DataExecucao) over (partition by ID_Servico order by ID),
                DataExecucao) as Intervalo
  from @TB_HORA
  order by ID_Serviço, ID;

In the article " Window functions window functions ) "you will find an explanation of how LAG , which is a window function .

If the version is earlier than 2012, here's another solution: 

-- código #2
with TB_HORA_seq as (
SELECT *, 
       seq= row_number() over (partition by ID_Servico order by ID)
  from @TB_HORA
)
SELECT T1.ID_Serviço, T1.ID, 
       T2.DataExecucao as DataExecucao_ant,
       T1.DataExecucao,
       datediff (second, T2.DataExecucao, T1.DataExecucao) as Intervalo
  from TB_HORA_seq as T1
       left join TB_HORA_seq as T2 on T2.ID_Servico = T1.ID_Servico 
                                      and T2.seq = (T1.seq-1)
  order by T1.ID_Serviço, T1.ID;
    
22.10.2018 / 16:07
Assignment of value to variable [closed] Receiving and handling JSON with PHP