Calculating Time Between Two Rows in SQL Server

Hello everyone. In this article, I will try to give information about calculating the time between two rows in SQL Server.

In SQL Server you may want to calculate the time between two rows in some cases.

You can easily do this by revising the sample code below.

--Tablonun oluşturulması

CREATE TABLE TestTable(
Id INT PRIMARY KEY IDENTITY(1,1),
IslemId INT,
Durum NVARCHAR(20),
Tarih DATETIME
)


--Tabloya veri eklenmesi

INSERT INTO TestTable (IslemId, Durum, Tarih)
	VALUES (100, N'Başladı', '2022-08-01 08:00:00.000'),
	(101, N'Başladı', '2022-08-02 07:30:00.000'),
	(100, N'Durdu', '2022-08-03 17:00:00.000'),
	(102, N'Başladı', '2022-08-04 08:00:00.000'),
	(102, N'Durdu', '2022-08-04 17:00:00.000'),
	(101, N'Durdu', '2022-08-05 18:00:00.000')

-- Tablonun kontrol edilmesi

SELECT * FROM TestTable

--İki satır arasındaki süreyi hesaplamak

SELECT
	IslemId, 
	CONVERT(DATE, testTablosu.Tarih) AS 'Başlama Zamanı',
	CONVERT(DATE, taskEnd.Tarih) AS 'Bitiş Zamanı',
	DATEDIFF(day, testTablosu.Tarih, taskEnd.Tarih) as 'Gün Farkı'
FROM TestTable testTablosu
OUTER APPLY (
	SELECT TOP(1)
		taskEnd.Tarih
	FROM TestTable taskEnd
	WHERE 
		taskEnd.Tarih > testTablosu.Tarih
		AND taskEnd.Durum = 'Durdu'
		AND taskEnd.IslemId = testTablosu.IslemId
	ORDER BY Tarih
	) taskEnd
WHERE
	testTablosu.Durum = 'Başladı'

When you create the table above and run the relevant codes, you will get a result as follows.

As you can see, we have calculated the time between two lines.

Good luck to everyone in business and life.