1

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

Dr. Mian
  • 3,334
  • 10
  • 45
  • 69
  • Seems you forgot the attachment. – Veverke Feb 19 '15 at 14:26
  • Which version of sql server do you use? – Giorgos Betsos Feb 19 '15 at 14:28
  • 1
    Use a self join to bring the two dates onto the same virtual row and then compare them. – Tab Alleman Feb 19 '15 at 14:32
  • possible duplicate of [How do i compare 2 rows from the same table (SQL Server)](http://stackoverflow.com/questions/510916/how-do-i-compare-2-rows-from-the-same-table-sql-server) – Tab Alleman Feb 19 '15 at 14:35
  • I have a feeling that what you are wanting to do is "close the gaps" between 1 row endTime and the "next" rows startTime. You might consider dropping the endTime column entirely and letting the next start time indicate the endTime of the previous. Much more dynamic and less prone to error. – Sean Lange Feb 19 '15 at 14:36
  • Yes but my boss is interested in actual readings :) nice idea but not possible in my case :( – Dr. Mian Feb 19 '15 at 14:54

1 Answers1

3

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

Michael Granowski
  • 76
  • 2
  • You should never rely on ordering of data in a relational database (Server can reorder your data at any time). Use some kind of index to join rows. – Fedor Hajdu Feb 19 '15 at 15:01
  • @FedorHajdu the above query is using a logical order here. That is what ROW_NUMBER is doing. – Sean Lange Feb 19 '15 at 15:11
  • @FedorHajdu - I think that here, ROW_NUMBER() is deterministic via the ORDER BY clause, assuming that the values in the 1st column ('starts') are unique? EDIT: oops, already answered – Michael Granowski Feb 19 '15 at 15:18
  • Thank you for the help, that is what I was looking for. Can you please clarify it a bit as I am new to SQL. How can I make a union of two existing columns or should I make another table or can you refer any webpage for understanding this query. Thanks a lot. – Dr. Mian Feb 20 '15 at 10:48
  • 1
    @Asbat - In the answer, tblDateTimeDifference is my mockup for the table in your example screenshot. Take only the code from the WITH downwards, substitute as follows - your table name for tblDateTimeDifference, startTime for 'starts' and endTime for 'ends' (assuming these are your column names). You can remove all the aliases to keep your column names. – Michael Granowski Feb 20 '15 at 11:23