How to get the difference in seconds with Sql Server

Question

I know that to get the difference of seconds between Beginning 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.
I need to know the difference between the eight records:
Explain:
What is the difference between Record 1 and Record 2;
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:
To the Case 2 the solution of Motta works perfectly, but after analyzing the real situation of the Database which is the following:

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 the Case 3 the way it is doesn’t work.
I need to return the difference in seconds, for example: only between the records ID_Servico = 49.

Answer 1

Here’s a solution for 2012 (or newer) version of SQL Server.

-- 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 Article "Window functions (functions window)"you find explanation about the functioning of LAG, which is a window Function.

---

If the version is before 2012, here is 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;

Answer 2

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