SQL "Where In" for empty subquery

Question

I have the following query, where the intention is to show each record with the time until the next record

Data:

gid         time                name
1010883478  29/03/2016 0:00:02  John
1010883527  29/03/2016 0:00:04  John
1010883578  29/03/2016 0:00:06  John

SQL:

  SELECT A.[gid]
       ,A.[time]
       ,A.[name]
       ,(B.[time] - A.[time]) as timeTilNext
  FROM [location] A CROSS JOIN [location] B 
  WHERE B.[gid] IN (
      SELECT MIN(C.[gid]) 
      FROM [location] C 
      WHERE C.[gid] > A.[gid] AND C.[name] = A.[name] )
  ORDER BY A.[gid]

Current Output:

gid         time                    name    timeTilNext
1010883478  2016-03-29 00:00:02.000 John    1900-01-01 00:00:02.000
1010883527  2016-03-29 00:00:04.000 John    1900-01-01 00:00:02.000

Expected Output:

gid         time                    name    timeTilNext
1010883478  2016-03-29 00:00:02.000 John    1900-01-01 00:00:02.000
1010883527  2016-03-29 00:00:04.000 John    1900-01-01 00:00:02.000
1010883578  2016-03-29 00:00:06.000 John    -1 (or whatever)

However, it does not show a record for the highest [gid] for a given [name] (only the second highest).

I'm hoping for the highest [gid] to show -1 for timeTilNext, to indicate that there is no more events.

Any ideas about how to modify my query?

Answer 1

In SQL Server 2012 you can use LEAD window function to get the value of the "next" row.

DECLARE @location TABLE ([gid] int, [time] datetime, [name] varchar(50));

INSERT INTO @location ([gid], [time], [name]) VALUES
(1010883478, '2016-03-29 00:00:02', 'John'),
(1010883527, '2016-03-29 00:00:04', 'John'),
(1010883578, '2016-03-29 00:00:06', 'John');

SELECT 
    A.[gid]
    ,A.[time]
    ,A.[name]
    ,LEAD(A.[time]) OVER(PARTITION BY A.[name] ORDER BY A.[gid]) AS NextTime
    ,ISNULL(DATEDIFF(second, A.[time], 
        LEAD(A.[time]) OVER(PARTITION BY A.[name] ORDER BY A.[gid])), -1) AS SecondsTillNext
FROM @location A
ORDER BY A.[gid];

Result

+------------+-------------------------+------+-------------------------+-----------------+
|    gid     |          time           | name |        NextTime         | SecondsTillNext |
+------------+-------------------------+------+-------------------------+-----------------+
| 1010883478 | 2016-03-29 00:00:02.000 | John | 2016-03-29 00:00:04.000 |               2 |
| 1010883527 | 2016-03-29 00:00:04.000 | John | 2016-03-29 00:00:06.000 |               2 |
| 1010883578 | 2016-03-29 00:00:06.000 | John | NULL                    |              -1 |
+------------+-------------------------+------+-------------------------+-----------------+

If the "next" row is not available, LEAD would return NULL. You can use ISNULL() to replace it with some non-null value if you want.