Get the time difference between the first and last row

Question

My table structure is like this

id date         Time                userid

17  2017-01-22  05:08:09.0000000    836232017
16  2017-01-22  05:08:05.0000000    836232017
11  2017-01-22  05:06:40.0000000    836232017
10  2017-01-22  05:06:38.0000000    836232017
7   2017-01-22  05:06:31.0000000    836232017

I need to get the time difference between the first and last value for the corresponding userid and date. Eg: the datediff for the userid 836232017 is between '05:08:09.0000000' and '05:06:31.0000000'. Please provide a solution for this.

I tried, but it takes several steps. is it possible to do the same in a single step. i am looking for that — Deepu T, May 19 '17 at 06:02
Check this post on DBA SE - https://dba.stackexchange.com/q/28482/4113 — Harsh Baid, May 19 '17 at 06:04
i tried like 'SELECT DATEDIFF(SECOND, MIN(times), MAX(times)) FROM table' — Deepu T, May 19 '17 at 06:07

score 5 · Accepted Answer · answered May 19 '17 at 06:21

5

try this

SELECT userid, 
DATEADD(SECOND, - DATEDIFF(SECOND, MAX(times), MIN(times)), @null) 
FROM table
group by userid

or this

SELECT userid, 
DATEDIFF(SECOND, Min(times), Max(times)) 
FROM table
group by userid

answered May 19 '17 at 06:21

Esperento57

16,521
3
39
45

Norbert Forgacs · Answer 2 · 2017-05-19T06:20:44.117

SELECT
Datediff (day,
(SELECT TOP(1) Date from dbo.YourTable),
(SELECT TOP(1) Date from dbo.YourTable ORDER BY id DESC))

So instead of day you can select minutes or hours or whatever, see on this link: https://www.w3schools.com/sql/func_datediff.asp

the only thing you have to add is the WHERE ID= the ID you want to select and the corresponding date, like this:

SELECT
Datediff (minute,
(SELECT TOP(1) Time from dbo.YourTable WHERE userID=123 AND date='yourdate'),
(SELECT TOP(1) Time from dbo.YourTable WHERE userID=123 AND date='yourdate' ORDER BY Time DESC))

instead of 123 you specify the ID you are looking for

Get the time difference between the first and last row

2 Answers2