clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591691 fiddles created (45709 in the last week).

CREATE TABLE myDtTm (theDateTime datetime);
 hidden batch(es)


INSERT INTO myDtTm (theDateTime) VALUES ('2012-08-28 12:05 PM') ,('2012-08-28 12:08 PM') ,('2012-08-28 12:11 PM') ,('2012-08-28 12:15 PM') ,('2012-08-28 12:20 PM') ,('2012-08-28 12:24 PM') ,('2012-08-28 12:28 PM') ,('2012-08-28 12:32 PM') ,('2012-08-28 12:36 PM') ,('2012-08-28 12:39 PM') ,('2012-08-28 12:42 PM') ,('2012-08-28 12:45 PM') ,('2012-08-28 4:01 PM') ,('2012-08-28 4:05 PM') ,('2012-08-28 4:08 PM') ,('2012-08-28 4:11 PM') ,('2012-08-28 4:14 PM') ,('2012-08-28 4:20 PM') ,('2012-08-28 4:23 PM') ,('2012-08-28 4:28 PM') ,('2012-08-28 4:33 PM') ,('2012-08-28 4:36 PM') ,('2012-08-28 4:40 PM') ,('2012-08-28 4:44 PM') ,('2012-08-28 4:47 PM') ,('2012-08-28 8:01 PM') ;
26 rows affected
 hidden batch(es)


SELECT theDateTime ,SUM(TimeDiff) OVER (ORDER BY theDateTime) FROM ( SELECT theDateTime ,CASE WHEN COALESCE(DATEDIFF(minute, LAG(theDateTime, 1) OVER (ORDER BY theDateTime), theDateTime), 100000) > 10 THEN 1 ELSE 0 END as TimeDiff FROM myDtTm ) sq ORDER BY theDateTime ;
theDateTime (No column name)
28/08/2012 12:05:00 1
28/08/2012 12:08:00 1
28/08/2012 12:11:00 1
28/08/2012 12:15:00 1
28/08/2012 12:20:00 1
28/08/2012 12:24:00 1
28/08/2012 12:28:00 1
28/08/2012 12:32:00 1
28/08/2012 12:36:00 1
28/08/2012 12:39:00 1
28/08/2012 12:42:00 1
28/08/2012 12:45:00 1
28/08/2012 16:01:00 2
28/08/2012 16:05:00 2
28/08/2012 16:08:00 2
28/08/2012 16:11:00 2
28/08/2012 16:14:00 2
28/08/2012 16:20:00 2
28/08/2012 16:23:00 2
28/08/2012 16:28:00 2
28/08/2012 16:33:00 2
28/08/2012 16:36:00 2
28/08/2012 16:40:00 2
28/08/2012 16:44:00 2
28/08/2012 16:47:00 2
28/08/2012 20:01:00 3
 hidden batch(es)