Calculate the difference between first event and last event for each date

Please login or register to vote for this query.

(click on this box to dismiss)
Q&A for SharePoint enthusiasts
CREATE TABLE #MyLogs
    (LogId int, LogDateTime datetime, DriverId int, EventCode int, AssetId int);
    
    
    
 Insert into #MyLogs
   (LogId, LogDateTime, DriverId, EventCode, AssetId)
 VALUES
(2126789,	N'2016-07-20 04:00:32.000',	2,	1	,1 ),
(2126801,	N'2016-07-20 04:02:38.000',	2,	2	,1 ),
(2126814,	N'2016-07-20 04:07:38.000',	2,	41	,1 ),
(2128658,	N'2016-07-20 09:30:45.000',	2,	1	,1 ),
(2128663,	N'2016-07-20 09:31:58.000',	2,	41	,1 ),
(2128664,	N'2016-07-20 09:32:08.000',	2,	41	,1 ),
(2128665,	N'2016-07-20 09:32:09.000',	2,	41	,1 ),
(2128666,	N'2016-07-20 09:32:19.000',	2,	41	,1 ),
(2128667,	N'2016-07-20 09:32:23.000',	2,	41	,1 ),
(2128668,	N'2016-07-20 09:32:29.000',	2,	41	,1 ),
(2128669,	N'2016-07-20 09:32:32.000',	2,	41	,1 ),
(2128670,	N'2016-07-20 09:32:43.000',	2,	7	,1 ),
(2128671,	N'2016-07-20 09:32:44.000',	2,	41	,1 ),
(2128672,	N'2016-07-20 09:32:52.000',	2,	41	,1 ),
(2128673,	N'2016-07-20 09:32:54.000',	2,	41	,1 ),
(2128678,	N'2016-07-20 09:33:55.000',	2,	41	,1 ),
(2128679,	N'2016-07-20 09:34:00.000',	2,	41	,1 ),
(2128682,	N'2016-07-20 09:34:47.000',	2,	41	,1 ),
(2128683,	N'2016-07-20 09:35:02.000',	2,	41	,1 ),
(2128684,	N'2016-07-20 09:35:04.000',	2,	41	,1 ),
(2128685,	N'2016-07-20 09:35:13.000',	2,	41	,1 ),
(2128686,	N'2016-07-20 09:35:14.000',	2,	41	,1 ),
(2128687,	N'2016-07-20 09:35:15.000',	2,	41	,1 ),
(2128690,	N'2016-07-20 09:35:37.000',	2,	2	,1 ),
(2128696,	N'2016-07-20 09:37:39.000',	2,	1	,1 ),
(2128697,	N'2016-07-20 09:37:43.000',	2,	41	,1 ),
(2128698,	N'2016-07-20 09:38:00.000',	2,	41	,1 ),
(2128699,	N'2016-07-20 09:38:02.000',	2,	41	,1 ),
(2128700,	N'2016-07-20 09:38:07.000',	2,	41	,1 ),
(2128709,	N'2016-07-20 09:40:12.000',	2,	41	,1 ),
(2128713,	N'2016-07-20 09:42:11.000',	2,	41	,1 ),
(2128714,	N'2016-07-20 09:42:23.000',	2,	41	,1 ),
(2128715,	N'2016-07-20 09:42:25.000',	2,	41	,1 ),
(2128723,	N'2016-07-20 09:42:57.000',	2,	41	,1 ),
(2128724,	N'2016-07-20 09:42:58.000',	2,	41	,1 ),
(2128726,	N'2016-07-20 09:43:00.000',	2,	41	,1 ),
(2128729,	N'2016-07-20 09:43:05.000',	2,	41	,1 ),
(2128730,	N'2016-07-20 09:43:08.000',	2,	41	,1 ),
(2128732,	N'2016-07-20 09:43:16.000',	2,	41	,1 ),
(2128731,	N'2016-07-20 09:43:17.000',	2,	6	,1 ),
(2128733,	N'2016-07-20 09:43:23.000',	2,	41	,1 ),
(2128735,	N'2016-07-20 09:43:56.000',	2,	2	,1 ),
(2128744,	N'2016-07-20 09:48:56.000',	2,	41	,1 ),
(2129186,	N'2016-07-20 11:18:25.000',	2,	1	,1 ),
(2129187,	N'2016-07-20 11:18:39.000',	2,	41	,1 ),
(2129188,	N'2016-07-20 11:18:46.000',	2,	41	,1 ),
(2129195,	N'2016-07-20 11:19:49.000',	2,	41	,1 ),
(2129196,	N'2016-07-20 11:19:54.000',	2,	41	,1 ),
(2129197,	N'2016-07-20 11:19:56.000',	2,	41	,1 ),
(2129198,	N'2016-07-20 11:20:06.000',	2,	7	,1 ),
(2129199,	N'2016-07-20 11:20:07.000',	2,	41	,1 ),
(2129200,	N'2016-07-20 11:20:15.000',	2,	41	,1 ),
(2129202,	N'2016-07-20 11:20:17.000',	2,	41	,1 ),
(2129207,	N'2016-07-20 11:21:48.000',	2,	41	,1 ),
(2129208,	N'2016-07-20 11:21:52.000',	2,	41	,1 ),
(2129214,	N'2016-07-20 11:23:03.000',	2,	41	,1 ),
(2129215,	N'2016-07-20 11:23:05.000',	2,	41	,1 ),
(2129216,	N'2016-07-20 11:23:10.000',	2,	41	,1 ),
(2129213,	N'2016-07-20 11:23:15.000',	2,	2	,1 ),
(2129233,	N'2016-07-20 11:28:15.000',	2,	41	,1 ),
(2129240,	N'2016-07-20 11:29:24.000',	2,	1	,1 ),
(2129243,	N'2016-07-20 11:29:58.000',	2,	41	,1 ),
(2129250,	N'2016-07-20 11:30:38.000',	2,	41	,1 ),
(2129249,	N'2016-07-20 11:30:39.000',	2,	6	,1 ),
(2129258,	N'2016-07-20 11:32:14.000',	2,	7	,1 ),
(2129259,	N'2016-07-20 11:32:15.000',	2,	41	,1 ),
(2129260,	N'2016-07-20 11:32:17.000',	2,	41	,1 ),
(2129267,	N'2016-07-20 11:33:58.000',	2,	41	,1 ),
(2129268,	N'2016-07-20 11:33:59.000',	2,	41	,1 ),
(2129269,	N'2016-07-20 11:34:00.000',	2,	41	,1 ),
(2129270,	N'2016-07-20 11:34:01.000',	2,	41	,1 ),
(2129271,	N'2016-07-20 11:34:02.000',	2,	41	,1 ),
(2129272,	N'2016-07-20 11:34:33.000',	2,	41	,1 ),
(2129273,	N'2016-07-20 11:34:34.000',	2,	41	,1 ),
(2129275,	N'2016-07-20 11:34:42.000',	2,	41	,1 ),
(2129277,	N'2016-07-20 11:34:51.000',	2,	41	,1 ),
(2129276,	N'2016-07-20 11:34:52.000',	2,	6	,1 ),
(2129280,	N'2016-07-20 11:34:53.000',	2,	41	,1 ),
(2129282,	N'2016-07-20 11:34:57.000',	2,	41	,1 ),
(2129283,	N'2016-07-20 11:34:58.000',	2,	41	,1 ),
(2129284,	N'2016-07-20 11:34:59.000',	2,	41	,1 ),
(2129286,	N'2016-07-20 11:35:18.000',	2,	2	,1 ),
(2129300,	N'2016-07-20 11:36:06.000',	2,	1	,1 ),
(2129304,	N'2016-07-20 11:36:56.000',	2,	41	,1 ),
(2129305,	N'2016-07-20 11:37:21.000',	2,	41	,1 ),
(2129308,	N'2016-07-20 11:38:25.000',	2,	2	,1 ),
(2129315,	N'2016-07-20 11:38:53.000',	2,	1	,1 ),
(2129318,	N'2016-07-20 11:38:58.000',	2,	2	,1 ),
(2129347,	N'2016-07-20 11:43:58.000',	2,	41	,1 ),
(2129549,	N'2016-07-20 12:09:52.000',	2,	1	,1 ),
(2129551,	N'2016-07-20 12:10:01.000',	2,	41	,1 ),
(2129552,	N'2016-07-20 12:10:03.000',	2,	41	,1 ),
(2129553,	N'2016-07-20 12:10:04.000',	2,	41	,1 ),
(2129554,	N'2016-07-20 12:10:15.000',	2,	41	,1 ),
(2129555,	N'2016-07-20 12:10:22.000',	2,	41	,1 ),
(2129556,	N'2016-07-20 12:10:28.000',	2,	41	,1 ),
(2129557,	N'2016-07-20 12:10:32.000',	2,	7	,1 ),
(2129558,	N'2016-07-20 12:10:33.000',	2,	41	,1 ),
(2129559,	N'2016-07-20 12:10:39.000',	2,	41	,1 ),
(2129560,	N'2016-07-20 12:10:40.000',	2,	41	,1 ),
(2129561,	N'2016-07-20 12:11:02.000',	2,	41	,1 ),
(2129562,	N'2016-07-20 12:11:03.000',	2,	41	,1 ),
(2129563,	N'2016-07-20 12:11:04.000',	2,	41	,1 ),
(2129571,	N'2016-07-20 12:13:11.000',	2,	41	,1 ),
(2129576,	N'2016-07-20 12:14:38.000',	2,	41	,1 ),
(2129577,	N'2016-07-20 12:14:39.000',	2,	41	,1 ),
(2129580,	N'2016-07-20 12:15:30.000',	2,	41	,1 ),
(2129581,	N'2016-07-20 12:15:31.000',	2,	41	,1 ),
(2129583,	N'2016-07-20 12:16:16.000',	2,	41	,1 ),
(2129582,	N'2016-07-20 12:16:17.000',	2,	6	,1 ),
(2129584,	N'2016-07-20 12:16:19.000',	2,	41	,1 ),
(2129586,	N'2016-07-20 12:16:31.000',	2,	41	,1 ),
(2129590,	N'2016-07-20 12:17:39.000',	2,	7	,1 ),
(2129591,	N'2016-07-20 12:17:40.000',	2,	41	,1 ),
(2129592,	N'2016-07-20 12:17:41.000',	2,	41	,1 ),
(2129597,	N'2016-07-20 12:19:22.000',	2,	41	,1 ),
(2129598,	N'2016-07-20 12:19:24.000',	2,	41	,1 ),
(2129600,	N'2016-07-20 12:19:34.000',	2,	41	,1 ),
(2129599,	N'2016-07-20 12:19:35.000',	2,	6	,1 ),
(2129601,	N'2016-07-20 12:19:41.000',	2,	41	,1 ),
(2129602,	N'2016-07-20 12:19:42.000',	2,	41	,1 ),
(2129604,	N'2016-07-20 12:19:50.000',	2,	41	,1 ),
(2129605,	N'2016-07-20 12:19:53.000',	2,	41	,1 ),
(2129609,	N'2016-07-20 12:20:08.000',	2,	2	,1 ),
(2129628,	N'2016-07-20 12:25:09.000',	2,	41	,1 ),
(2129633,	N'2016-07-20 12:26:46.000',	2,	1	,1 ),
(2129634,	N'2016-07-20 12:26:57.000',	2,	41	,1 ),
(2129635,	N'2016-07-20 12:27:12.000',	2,	41	,1 ),
(2129636,	N'2016-07-20 12:27:14.000',	2,	41	,1 ),
(2129646,	N'2016-07-20 12:29:56.000',	2,	2	,1 ),
(2129648,	N'2016-07-20 12:30:25.000',	2,	1	,1 ),
(2129650,	N'2016-07-20 12:30:37.000',	2,	41	,1 ),
(2129652,	N'2016-07-20 12:31:02.000',	2,	41	,1 ),
(2129653,	N'2016-07-20 12:31:08.000',	2,	41	,1 ),
(2129654,	N'2016-07-20 12:31:14.000',	2,	7	,1 ),
(2129655,	N'2016-07-20 12:31:15.000',	2,	41	,1 ),
(2129656,	N'2016-07-20 12:31:18.000',	2,	41	,1 ),
(2129657,	N'2016-07-20 12:31:30.000',	2,	41	,1 ),
(2129658,	N'2016-07-20 12:31:32.000',	2,	41	,1 ),
(2129662,	N'2016-07-20 12:32:01.000',	2,	41	,1 ),
(2129663,	N'2016-07-20 12:32:02.000',	2,	41	,1 ),
(2129664,	N'2016-07-20 12:32:03.000',	2,	41	,1 ),
(2129665,	N'2016-07-20 12:32:28.000',	2,	41	,1 ),
(2129666,	N'2016-07-20 12:32:37.000',	2,	41	,1 ),
(2129667,	N'2016-07-20 12:32:40.000',	2,	41	,1 ),
(2129668,	N'2016-07-20 12:32:41.000',	2,	41	,1 ),
(2129669,	N'2016-07-20 12:32:50.000',	2,	2	,1 ),
(2129698,	N'2016-07-20 12:37:50.000',	2,	41	,1 ),
(2129878,	N'2016-07-20 13:02:31.000',	2,	1	,1 ),
(2129880,	N'2016-07-20 13:02:42.000',	2,	41	,1 ),
(2129891,	N'2016-07-20 13:03:15.000',	2,	41	,1 ),
(2129893,	N'2016-07-20 13:03:18.000',	2,	41	,1 ),
(2129895,	N'2016-07-20 13:03:50.000',	2,	41	,1 ),
(2129896,	N'2016-07-20 13:03:51.000',	2,	41	,1 ),
(2129897,	N'2016-07-20 13:03:53.000',	2,	41	,1 ),
(2129898,	N'2016-07-20 13:04:21.000',	2,	41	,1 ),
(2129899,	N'2016-07-20 13:04:22.000',	2,	41	,1 ),
(2129900,	N'2016-07-20 13:04:23.000',	2,	41	,1 ),
(2129904,	N'2016-07-20 13:04:32.000',	2,	41	,1 ),
(2129903,	N'2016-07-20 13:04:33.000',	2,	6	,1 ),
(2129905,	N'2016-07-20 13:04:37.000',	2,	41	,1 ),
(2129906,	N'2016-07-20 13:04:38.000',	2,	41	,1 ),
(2129912,	N'2016-07-20 13:04:49.000',	2,	41	,1 ),
(2129913,	N'2016-07-20 13:04:53.000',	2,	41	,1 ),
(2129917,	N'2016-07-20 13:04:55.000',	2,	41	,1 ),
(2129921,	N'2016-07-20 13:05:19.000',	2,	2	,1 ),
(2129948,	N'2016-07-20 13:10:19.000',	2,	41	,1 ),
(2133558,	N'2016-07-21 04:55:52.000',	2,	1	,1 ),
(2133561,	N'2016-07-21 04:56:00.000',	2,	41	,1 ),
(2133562,	N'2016-07-21 04:56:23.000',	2,	41	,1 ),
(2133566,	N'2016-07-21 04:57:29.000',	2,	41	,1 ),
(2133567,	N'2016-07-21 04:57:35.000',	2,	41	,1 ),
(2133568,	N'2016-07-21 04:57:36.000',	2,	41	,1 ),
(2133569,	N'2016-07-21 04:57:42.000',	2,	41	,1 ),
(2133571,	N'2016-07-21 04:57:46.000',	2,	7	,1 ),
(2133572,	N'2016-07-21 04:57:47.000',	2,	41	,1 ),
(2133573,	N'2016-07-21 04:57:57.000',	2,	41	,1 ),
(2133574,	N'2016-07-21 04:57:59.000',	2,	41	,1 ),
(2133585,	N'2016-07-21 04:59:20.000',	2,	41	,1 ),
(2133582,	N'2016-07-21 04:59:31.000',	2,	2	,1 ),
(2133635,	N'2016-07-21 05:04:31.000',	2,	41	,1 ),
(2133637,	N'2016-07-21 05:05:26.000',	2,	1	,1 ),
(2133646,	N'2016-07-21 05:06:46.000',	2,	41	,1 ),
(2133647,	N'2016-07-21 05:06:48.000',	2,	41	,1 ),
(2133654,	N'2016-07-21 05:08:14.000',	2,	41	,1 ),
(2133655,	N'2016-07-21 05:08:15.000',	2,	41	,1 ),
(2133656,	N'2016-07-21 05:08:30.000',	2,	41	,1 ),
(2133657,	N'2016-07-21 05:08:32.000',	2,	41	,1 ),
(2133658,	N'2016-07-21 05:09:06.000',	2,	41	,1 ),
(2133659,	N'2016-07-21 05:09:25.000',	2,	2	,1 ),
(2133664,	N'2016-07-21 05:11:12.000',	2,	1	,1 ),
(2133665,	N'2016-07-21 05:11:18.000',	2,	2	,1 ),
(2133681,	N'2016-07-21 05:12:56.000',	2,	1	,1 ),
(2133682,	N'2016-07-21 05:13:13.000',	2,	41	,1 ),
(2133683,	N'2016-07-21 05:13:14.000',	2,	41	,1 ),
(2133684,	N'2016-07-21 05:13:16.000',	2,	41	,1 ),
(2133687,	N'2016-07-21 05:13:38.000',	2,	41	,1 ),
(2133688,	N'2016-07-21 05:13:39.000',	2,	41	,1 ),
(2133689,	N'2016-07-21 05:13:40.000',	2,	41	,1 ),
(2133690,	N'2016-07-21 05:13:55.000',	2,	41	,1 ),
(2133692,	N'2016-07-21 05:13:57.000',	2,	41	,1 ),
(2133694,	N'2016-07-21 05:14:28.000',	2,	41	,1 ),
(2133708,	N'2016-07-21 05:16:18.000',	2,	41	,1 ),
(2133720,	N'2016-07-21 05:17:23.000',	2,	41	,1 ),
(2133722,	N'2016-07-21 05:17:53.000',	2,	41	,1 ),
(2133723,	N'2016-07-21 05:17:55.000',	2,	41	,1 ),
(2133724,	N'2016-07-21 05:17:59.000',	2,	2	,1 ),
(2133737,	N'2016-07-21 05:22:59.000',	2,	41	,1 ),
(2133741,	N'2016-07-21 05:24:45.000',	2,	1	,1 ),
(2133744,	N'2016-07-21 05:25:05.000',	2,	41	,1 ),
(2133758,	N'2016-07-21 05:26:28.000',	2,	41	,1 ),
(2133759,	N'2016-07-21 05:26:36.000',	2,	41	,1 ),
(2133769,	N'2016-07-21 05:28:00.000',	2,	41	,1 ),
(2133773,	N'2016-07-21 05:28:36.000',	2,	41	,1 ),
(2133776,	N'2016-07-21 05:28:38.000',	2,	41	,1 ),
(2133777,	N'2016-07-21 05:28:46.000',	2,	41	,1 ),
(2133780,	N'2016-07-21 05:28:52.000',	2,	41	,1 ),
(2133781,	N'2016-07-21 05:28:54.000',	2,	41	,1 ),
(2133784,	N'2016-07-21 05:29:16.000',	2,	41	,1 ),
(2133804,	N'2016-07-21 05:30:05.000',	2,	41	,1 ),
(2133805,	N'2016-07-21 05:30:07.000',	2,	41	,1 ),
(2133806,	N'2016-07-21 05:30:17.000',	2,	41	,1 ),
(2133807,	N'2016-07-21 05:30:18.000',	2,	41	,1 ),
(2133808,	N'2016-07-21 05:30:26.000',	2,	41	,1 ),
(2133810,	N'2016-07-21 05:30:37.000',	2,	41	,1 ),
(2133809,	N'2016-07-21 05:30:38.000',	2,	6	,1 ),
(2133812,	N'2016-07-21 05:30:42.000',	2,	41	,1 ),
(2133813,	N'2016-07-21 05:30:43.000',	2,	41	,1 ),
(2133822,	N'2016-07-21 05:31:22.000',	2,	2	,1 ),
(2133846,	N'2016-07-21 05:36:22.000',	2,	41	,1 ),
(2133987,	N'2016-07-21 06:21:42.000',	2,	1	,1 ),
(2133988,	N'2016-07-21 06:21:48.000',	2,	41	,1 ),
(2133989,	N'2016-07-21 06:21:54.000',	2,	41	,1 ),
(2134001,	N'2016-07-21 06:23:31.000',	2,	41	,1 ),
(2134002,	N'2016-07-21 06:23:37.000',	2,	41	,1 ),
(2134003,	N'2016-07-21 06:23:45.000',	2,	41	,1 ),
(2134005,	N'2016-07-21 06:24:01.000',	2,	7	,1 ),
(2134006,	N'2016-07-21 06:24:02.000',	2,	41	,1 ),
(2134007,	N'2016-07-21 06:24:03.000',	2,	41	,1 ),
(2134009,	N'2016-07-21 06:24:06.000',	2,	41	,1 ),
(2134020,	N'2016-07-21 06:25:37.000',	2,	41	,1 ),
(2134021,	N'2016-07-21 06:25:39.000',	2,	41	,1 ),
(2134022,	N'2016-07-21 06:25:41.000',	2,	41	,1 ),
(2134023,	N'2016-07-21 06:25:57.000',	2,	41	,1 ),
(2134026,	N'2016-07-21 06:26:06.000',	2,	2	,1 ),
(2134055,	N'2016-07-21 06:30:40.000',	2,	1	,1 ),
(2134056,	N'2016-07-21 06:30:57.000',	2,	41	,1 ),
(2134057,	N'2016-07-21 06:31:34.000',	2,	2	,1 ),
(2134081,	N'2016-07-21 06:33:18.000',	2,	1	,1 ),
(2134087,	N'2016-07-21 06:34:21.000',	2,	41	,1 ),
(2134090,	N'2016-07-21 06:34:53.000',	2,	41	,1 ),
(2134091,	N'2016-07-21 06:34:54.000',	2,	41	,1 ),
(2134093,	N'2016-07-21 06:34:56.000',	2,	41	,1 ),
(2134106,	N'2016-07-21 06:37:11.000',	2,	41	,1 ),
(2134107,	N'2016-07-21 06:37:13.000',	2,	41	,1 ),
(2134111,	N'2016-07-21 06:37:43.000',	2,	41	,1 ),
(2134112,	N'2016-07-21 06:37:45.000',	2,	41	,1 ),
(2134113,	N'2016-07-21 06:37:53.000',	2,	41	,1 ),
(2134115,	N'2016-07-21 06:38:01.000',	2,	41	,1 ),
(2134114,	N'2016-07-21 06:38:02.000',	2,	6	,1 ),
(2134116,	N'2016-07-21 06:38:03.000',	2,	41	,1 ),
(2134117,	N'2016-07-21 06:38:07.000',	2,	41	,1 ),
(2134130,	N'2016-07-21 06:38:45.000',	2,	41	,1 ),
(2134129,	N'2016-07-21 06:38:56.000',	2,	2	,1 ),
(2134159,	N'2016-07-21 06:43:56.000',	2,	41	,1 ),
(2135175,	N'2016-07-21 09:52:15.000',	2,	1	,1 ),
(2135176,	N'2016-07-21 09:52:19.000',	2,	41	,1 ),
(2135177,	N'2016-07-21 09:52:25.000',	2,	41	,1 ),
(2135190,	N'2016-07-21 09:52:36.000',	2,	2	,1 ),
(2135237,	N'2016-07-21 09:57:36.000',	2,	41	,1 ),
(2135340,	N'2016-07-21 10:06:05.000',	2,	1	,1 ),
(2135343,	N'2016-07-21 10:06:39.000',	2,	41	,1 ),
(2135346,	N'2016-07-21 10:07:15.000',	2,	41	,1 ),
(2135347,	N'2016-07-21 10:07:21.000',	2,	41	,1 ),
(2135348,	N'2016-07-21 10:07:22.000',	2,	41	,1 ),
(2135349,	N'2016-07-21 10:07:40.000',	2,	7	,1 ),
(2135350,	N'2016-07-21 10:07:41.000',	2,	41	,1 ),
(2135351,	N'2016-07-21 10:07:44.000',	2,	41	,1 ),
(2135352,	N'2016-07-21 10:08:10.000',	2,	41	,1 ),
(2135353,	N'2016-07-21 10:08:11.000',	2,	41	,1 ),
(2135356,	N'2016-07-21 10:08:14.000',	2,	41	,1 ),
(2135359,	N'2016-07-21 10:08:33.000',	2,	2	,1 ),
(2135375,	N'2016-07-21 10:10:29.000',	2,	1	,1 ),
(2135377,	N'2016-07-21 10:10:37.000',	2,	41	,1 ),
(2135399,	N'2016-07-21 10:13:07.000',	2,	41	,1 ),
(2135405,	N'2016-07-21 10:13:46.000',	2,	41	,1 ),
(2135407,	N'2016-07-21 10:13:48.000',	2,	41	,1 ),
(2135408,	N'2016-07-21 10:13:49.000',	2,	41	,1 ),
(2135409,	N'2016-07-21 10:14:10.000',	2,	2	,1 ),
(2135464,	N'2016-07-21 10:18:49.000',	2,	41	,1 ),
(2135541,	N'2016-07-21 10:24:13.000',	2,	1	,1 ),
(2135547,	N'2016-07-21 10:24:40.000',	2,	41	,1 ),
(2135550,	N'2016-07-21 10:24:42.000',	2,	41	,1 ),
(2135551,	N'2016-07-21 10:24:49.000',	2,	41	,1 ),
(2135555,	N'2016-07-21 10:24:57.000',	2,	41	,1 ),
(2135556,	N'2016-07-21 10:24:58.000',	2,	41	,1 ),
(2135559,	N'2016-07-21 10:24:59.000',	2,	41	,1 ),
(2135561,	N'2016-07-21 10:25:05.000',	2,	2	,1 ),
(2135625,	N'2016-07-21 10:30:05.000',	2,	41	,1 ),
(2135649,	N'2016-07-21 10:33:02.000',	2,	1	,1 ),
(2135651,	N'2016-07-21 10:33:20.000',	2,	41	,1 ),
(2135653,	N'2016-07-21 10:33:22.000',	2,	41	,1 ),
(2135654,	N'2016-07-21 10:33:26.000',	2,	41	,1 ),
(2135678,	N'2016-07-21 10:35:19.000',	2,	41	,1 ),
(2135679,	N'2016-07-21 10:36:14.000',	2,	41	,1 ),
(2135680,	N'2016-07-21 10:36:15.000',	2,	41	,1 ),
(2135681,	N'2016-07-21 10:36:16.000',	2,	41	,1 ),
(2135682,	N'2016-07-21 10:36:17.000',	2,	41	,1 ),
(2135685,	N'2016-07-21 10:36:41.000',	2,	41	,1 ),
(2135686,	N'2016-07-21 10:36:42.000',	2,	41	,1 ),
(2135688,	N'2016-07-21 10:37:08.000',	2,	2	,1 ),
(2135702,	N'2016-07-21 10:42:08.000',	2,	41	,1 ),
(2135873,	N'2016-07-21 10:52:57.000',	2,	1	,1 ),
(2135887,	N'2016-07-21 10:53:40.000',	2,	2	,1 ),
(2135958,	N'2016-07-21 10:58:40.000',	2,	41	,1 ),
(2136126,	N'2016-07-21 11:15:05.000',	2,	1	,1 ),
(2136128,	N'2016-07-21 11:15:23.000',	2,	41	,1 ),
(2136130,	N'2016-07-21 11:15:27.000',	2,	41	,1 ),
(2136135,	N'2016-07-21 11:15:40.000',	2,	41	,1 ),
(2136136,	N'2016-07-21 11:15:42.000',	2,	41	,1 ),
(2136137,	N'2016-07-21 11:15:44.000',	2,	41	,1 ),
(2136138,	N'2016-07-21 11:15:46.000',	2,	41	,1 ),
(2136140,	N'2016-07-21 11:16:20.000',	2,	41	,1 ),
(2136149,	N'2016-07-21 11:17:25.000',	2,	41	,1 ),
(2136150,	N'2016-07-21 11:17:27.000',	2,	41	,1 ),
(2136152,	N'2016-07-21 11:17:29.000',	2,	41	,1 ),
(2136151,	N'2016-07-21 11:17:30.000',	2,	6	,1 ),
(2136153,	N'2016-07-21 11:17:38.000',	2,	41	,1 ),
(2136154,	N'2016-07-21 11:17:42.000',	2,	41	,1 ),
(2136156,	N'2016-07-21 11:18:01.000',	2,	41	,1 ),
(2136158,	N'2016-07-21 11:18:03.000',	2,	41	,1 ),
(2136159,	N'2016-07-21 11:18:05.000',	2,	41	,1 ),
(2136160,	N'2016-07-21 11:18:06.000',	2,	41	,1 ),
(2136161,	N'2016-07-21 11:18:08.000',	2,	41	,1 ),
(2136162,	N'2016-07-21 11:18:09.000',	2,	41	,1 ),
(2136163,	N'2016-07-21 11:18:10.000',	2,	41	,1 ),
(2136164,	N'2016-07-21 11:18:14.000',	2,	41	,1 ),
(2136165,	N'2016-07-21 11:18:20.000',	2,	41	,1 ),
(2136170,	N'2016-07-21 11:19:32.000',	2,	41	,1 ),
(2136171,	N'2016-07-21 11:19:40.000',	2,	7	,1 ),
(2136172,	N'2016-07-21 11:19:41.000',	2,	41	,1 ),
(2136173,	N'2016-07-21 11:19:42.000',	2,	41	,1 ),
(2136177,	N'2016-07-21 11:21:26.000',	2,	41	,1 ),
(2136188,	N'2016-07-21 11:23:49.000',	2,	2	,1 ),
(2136194,	N'2016-07-21 11:26:42.000',	2,	1	,1 ),
(2136195,	N'2016-07-21 11:26:45.000',	2,	41	,1 ),
(2136199,	N'2016-07-21 11:27:53.000',	2,	41	,1 ),
(2136200,	N'2016-07-21 11:27:54.000',	2,	41	,1 ),
(2136201,	N'2016-07-21 11:27:56.000',	2,	41	,1 ),
(2136202,	N'2016-07-21 11:28:34.000',	2,	41	,1 ),
(2136205,	N'2016-07-21 11:28:43.000',	2,	41	,1 ),
(2136204,	N'2016-07-21 11:28:44.000',	2,	6	,1 ),
(2136206,	N'2016-07-21 11:28:50.000',	2,	41	,1 ),
(2136207,	N'2016-07-21 11:29:04.000',	2,	41	,1 ),
(2136212,	N'2016-07-21 11:29:20.000',	2,	2	,1 ),
(2136232,	N'2016-07-21 11:34:20.000',	2,	41	,1 ),
(2136290,	N'2016-07-21 11:41:46.000',	2,	1	,1 ),
(2136291,	N'2016-07-21 11:42:04.000',	2,	41	,1 ),
(2136294,	N'2016-07-21 11:43:06.000',	2,	2	,1 ),
(2136314,	N'2016-07-21 11:48:07.000',	2,	41	,1 ),
(2136646,	N'2016-07-21 12:15:29.000',	2,	1	,1 ),
(2136657,	N'2016-07-21 12:16:57.000',	2,	41	,1 ),
(2136659,	N'2016-07-21 12:17:24.000',	2,	2	,1 ),
(2136678,	N'2016-07-21 12:22:25.000',	2,	41	,1 ),
(2136687,	N'2016-07-21 12:25:28.000',	2,	1	,1 ),
(2136688,	N'2016-07-21 12:25:32.000',	2,	41	,1 ),
(2136690,	N'2016-07-21 12:25:54.000',	2,	2	,1 ),
(2136702,	N'2016-07-21 12:30:54.000',	2,	41	,1 );

SELECT LogDate, StartOfDay, EndOfDay, DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds
FROM
  (
    SELECT
        CAST(LogDateTime AS DATE) AS LogDate,
        MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay,
        MAX(CASE WHEN EventCode = 2 THEN LogDateTime END) AS EndOfDay
    FROM #MyLogs
    WHERE EventCode in (1,2)
    GROUP BY
        CAST(LogDateTime AS DATE)
  ) AS dt 
;
edited nov 22
anonymous
created jul 21 16
Dawood.Awan
Enter Parameters

Options: Text-only results Include execution plan
Switch to meta site
Hold tight while we fetch your results
Results[:current/:total] Messages Graph Execution Plan
Download CSV Download XML
:records returned in :time ms:cached