CODEWITHSUNDEEP
sql-serverdatabase
drmaa
drmaa

Reputation: 3674

How to find the difference between two date cells of different rows in sql server 2012?

I have a sql table with each record start and end time as shown in the picture. I am interested in finding difference between them. I found them by using datediff(second, starttime, endtime) but it gives the difference in values of the same rows. However I want to see if the next 'date/time' starts from where the previous row end 'date/time' finished or there is some gap for each row? I am indicating the sample cells in which I want to find the difference if any or 0. I want to find for the complete columns. Thanks

enter image description here

Upvotes: 1

Views: 1544

Answers (1)

Michael Granowski
Michael Granowski

Reputation: 76

Is this what you are looking for?

IF EXISTS (SELECT NULL FROM information_schema.TABLES WHERE TABLE_NAME = 'tblDateTimeDifference')
    DROP TABLE tblDateTimeDifference

CREATE TABLE tblDateTimeDifference (
    starts datetime, ends datetime
)

INSERT INTO tblDateTimeDifference
    SELECT '2015-02-19 00:00:00.000', '2015-02-19 00:01:00.000' UNION
    SELECT '2015-02-19 00:01:00.000', '2015-02-19 00:02:00.000' UNION
    SELECT '2015-02-19 00:02:00.000', '2015-02-19 00:03:00.000' UNION
    SELECT '2015-02-19 00:03:30.000', '2015-02-19 00:04:00.000' UNION
    SELECT '2015-02-19 00:04:00.000', '2015-02-19 00:05:00.000' UNION
    SELECT '2015-02-19 00:05:00.000', '2015-02-19 00:06:00.000' UNION
    SELECT '2015-02-19 00:06:00.000', '2015-02-19 00:07:00.000'
;
WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY starts) AS row_num, starts, ends
    FROM tblDateTimeDifference
)
SELECT a.row_num AS compare_me, b.row_num AS to_me, a.ends AS compare_me_ends, b.starts AS compare_to_me_starts, DATEDIFF(ss, a.ends, b.starts) AS gap
FROM cte a INNER JOIN cte b ON a.row_num = b.row_num - 1

Here is a fiddle:

http://sqlfiddle.com/#!3/9db054/1

Upvotes: 3

Related Questions