amateur amateur - 1 month ago 6
SQL Question

find longest duration between times

I am running a SQL query in SQL Server that pulls back a list of times. eg

00:01:45
00:01:55
00:02:25
00:05:33
00:10:45
00:11:01
00:13:45


I want to some how with SQL return a single time that represents to the largest duration between the times above (which is the output of a query). Is it possible to do this via SQL and if so, how?

Answer

You can use the analytic functions LAG or LEAD to do this. By performing a DATEDIFF calculation on either the previous or next row (ordering by the time) we can determine the duration between each ordered pair. The only thing left to do is take the MAX.

with Data as (
         select a.TheTime
              , DateDiff(minute, Lag(a.TheTime, 1, a.TheTime) over(order by a.TheTime asc), a.TheTime) as Duration
         from (values
             (Convert(time(0), N'00:01:45'))
           , (Convert(time(0), N'00:01:55'))
           , (Convert(time(0), N'00:02:25'))
           , (Convert(time(0), N'00:05:33'))
           , (Convert(time(0), N'00:10:45'))
           , (Convert(time(0), N'00:11:01'))
           , (Convert(time(0), N'00:13:45'))
         ) as a (TheTime)
     )
select Max(a.Duration) as MaxDuration
from Data as a;