Post reply

Time Duration for Middle Dates

  • November 22, 2010 at 1:20 pm

    #241572

    Hi,

    How would I get the duration of time for the middle dates part? For example, if we take Darnel Smith on 2010-11-02, this date doesn't have have middle, but on 2010-11-06, the middle dates are 2010-11-06 04:39:00 PM and 2010-11-06 05:21:00 PM, so I would like to take the duration for this time which would be (i think) 42 minutes. For Richard William the middle dates for 2010-11-02 are 2010-11-02 12:00:00 PM and 2010-11-02 12:30:00 PM so the duration for this time would be 30 minutes.

    CREATE TABLE #EETime

    (PersonName VARCHAR(200)

    ,PersonID VARCHAR(10)

    ,dateandtimedatetime)

    INSERT INTO #EETime

    SELECT 'Darnel Smith','ds345','2010-11-02 02:07:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-02 07:42:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-06 07:08:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-06 04:39:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-06 05:21:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-07 12:00:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-07 07:20:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-07 08:57:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-07 09:25:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-08 12:56:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-08 11:03:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-09 08:51:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-13 06:56:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-13 08:21:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-13 09:11:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-13 11:28:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-14 07:26:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-14 12:22:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-14 12:45:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-14 10:29:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-14 10:51:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-15 12:38:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-15 10:52:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-16 07:54:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-20 07:11:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-20 01:30:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-20 01:40:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-20 09:07:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-20 09:24:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-20 11:26:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-21 07:24:00 AM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-21 12:10:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-21 12:33:00 PM' UNION

    SELECT 'Darnel Smith','ds345','2010-11-21 11:30:00 PM' UNION

    SELECT 'Sam Davis','sd382','2010-11-01 08:35:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-02 09:22:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-03 09:04:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-04 09:00:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-08 08:35:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-09 08:59:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-16 08:42:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-17 03:14:00 PM' UNION

    SELECT 'Sam Davis','sd382','2010-11-18 08:39:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-19 08:43:00 AM' UNION

    SELECT 'Sam Davis','sd382','2010-11-22 08:37:00 AM' UNION

    SELECT 'Grage Stone','gs341','2010-11-01 03:59:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-01 10:02:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-02 03:51:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-02 10:01:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-03 03:58:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-03 10:09:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-04 12:39:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-04 02:31:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-04 03:53:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-04 05:17:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-09 05:32:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-09 10:01:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-10 03:58:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-10 10:24:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-11 03:46:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-11 10:00:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-15 03:57:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-15 10:02:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-16 04:03:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-16 10:04:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-17 04:06:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-17 10:04:00 PM' UNION

    SELECT 'Grage Stone','gs341','2010-11-18 07:57:00 AM' UNION

    SELECT 'Grage Stone','gs341','2010-11-18 04:08:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-10-31 04:00:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-10-31 04:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-10-31 08:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-10-31 03:00:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-01 02:00:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-01 03:00:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-01 07:50:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-01 11:08:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-02 05:20:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-02 05:50:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-02 07:35:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-03 11:00:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-04 04:00:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-04 04:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-04 07:50:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-04 11:04:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-05 05:29:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-05 05:49:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-05 07:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-05 11:03:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-06 03:32:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-06 03:53:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-06 07:58:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-06 11:05:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-07 01:42:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-07 02:07:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-07 08:27:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-08 11:01:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-09 06:08:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-09 06:26:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-09 07:33:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-10 11:07:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-11 05:01:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-11 05:24:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-11 07:27:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-11 10:59:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-12 05:23:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-12 05:42:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-12 07:36:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-12 11:00:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-13 08:10:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-13 11:00:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-14 04:00:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-14 04:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-14 09:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-14 11:04:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-15 01:40:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-15 02:10:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-15 07:51:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-15 11:00:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-16 04:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-16 05:00:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-16 07:51:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-18 11:11:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-19 08:07:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-20 11:04:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-21 04:46:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-21 05:13:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-21 07:30:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-21 11:04:00 PM' UNION

    SELECT 'Val Steve','vs469','2010-11-22 05:31:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-22 05:52:00 AM' UNION

    SELECT 'Val Steve','vs469','2010-11-22 07:28:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-02 07:40:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-02 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-02 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-02 05:06:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-03 08:01:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-03 10:18:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-04 07:25:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-04 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-04 01:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-04 05:32:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-05 07:35:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-05 05:24:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-08 06:50:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-08 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-08 01:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-08 05:48:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-09 07:05:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-09 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-09 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-09 02:23:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-10 07:50:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-10 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-10 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-10 05:11:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-11 07:49:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-11 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-11 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-11 05:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-12 07:30:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-12 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-12 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-12 02:41:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-15 08:02:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-15 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-15 01:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-15 05:16:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-16 07:42:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-16 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-16 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-16 05:11:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-17 07:38:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-17 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-17 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-17 05:07:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-19 07:52:00 AM' UNION

    SELECT 'Richard Williams','rw319','2010-11-19 12:00:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-19 12:30:00 PM' UNION

    SELECT 'Richard Williams','rw319','2010-11-19 04:07:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-10-31 06:51:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-10-31 03:14:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-10-31 03:44:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-10-31 07:45:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-01 09:20:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-01 03:20:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-01 03:50:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-01 07:11:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-04 09:55:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-04 03:00:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-04 03:19:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-04 06:25:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-05 06:35:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-05 03:22:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-06 06:45:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-06 02:07:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-06 02:34:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-06 04:17:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-09 10:20:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-09 03:35:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-09 04:06:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-09 07:55:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-10 10:27:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-10 03:32:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-10 04:00:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-10 07:40:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-11 06:44:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-11 02:27:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-11 02:55:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-11 03:52:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-12 08:44:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-12 03:35:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-12 04:03:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-12 05:36:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-13 06:46:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-13 02:11:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-13 02:32:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-13 03:49:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-14 06:44:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-14 04:06:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-17 08:52:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-17 02:25:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-17 02:55:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-17 06:22:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-18 08:53:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-18 06:00:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-19 08:54:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-19 03:21:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-19 03:49:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-19 05:27:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-20 08:37:00 AM' UNION

    SELECT 'Kelly Michales','km326','2010-11-20 02:00:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-20 02:29:00 PM' UNION

    SELECT 'Kelly Michales','km326','2010-11-20 06:11:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-10-31 12:39:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-01 03:40:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-02 12:39:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-02 03:55:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-03 12:11:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-03 03:50:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-04 12:06:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-04 03:48:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-05 12:24:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-06 03:43:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-07 12:26:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-07 03:50:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-08 12:12:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-08 03:47:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-08 10:03:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-08 10:26:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-09 12:05:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-10 03:47:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-11 12:54:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-11 03:47:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-12 12:24:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-12 03:46:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-12 09:02:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-12 09:27:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-12 11:55:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-15 03:52:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-16 12:12:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-16 03:50:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-17 12:36:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-17 03:46:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-18 12:06:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-19 03:51:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-19 08:54:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-19 09:44:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-20 12:38:00 AM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-20 03:53:00 PM' UNION

    SELECT 'Jim Brooks','jb326','2010-11-20 11:52:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-10-31 09:43:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-10-31 09:00:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-01 09:43:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-01 02:30:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-02 02:04:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-02 08:47:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-03 09:40:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-03 04:06:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-03 06:03:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-03 10:33:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-04 10:16:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-04 05:28:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-05 11:15:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-05 05:19:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-06 10:15:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-06 03:30:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-06 05:39:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-06 09:13:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-07 10:15:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-07 02:59:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-07 03:00:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-08 12:58:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-08 09:51:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-08 05:00:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-09 11:36:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-09 04:57:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-10 12:11:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-10 06:45:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-13 10:18:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-13 04:03:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-14 09:53:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-14 02:37:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-14 04:03:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-15 01:14:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-15 11:20:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-15 12:38:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-20 09:42:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-20 10:27:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-21 10:21:00 AM' UNION

    SELECT 'Kim Porter','kp716','2010-11-21 05:23:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-21 08:25:00 PM' UNION

    SELECT 'Kim Porter','kp716','2010-11-21 10:57:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-01 10:56:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-02 07:37:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-02 10:45:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-03 07:47:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-03 10:39:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-04 07:58:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-04 11:17:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-05 07:15:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-08 10:52:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-09 06:57:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-09 10:52:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-10 07:50:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-10 11:08:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-11 07:32:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-11 10:57:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-12 07:57:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-13 06:45:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-14 07:28:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-14 06:59:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-15 08:05:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-15 10:49:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-16 07:22:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-16 11:01:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-17 07:20:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-17 11:00:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-18 07:49:00 AM' UNION

    SELECT 'Larry Summers','ls428','2010-11-18 11:04:00 PM' UNION

    SELECT 'Larry Summers','ls428','2010-11-19 07:37:00 AM'

    select * from #EETime

  • November 22, 2010 at 1:23 pm

    #1254002

    I'm not entirely clear on what you mean by "middle dates", but duration queries are usually done by using datediff on the two values.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 22, 2010 at 1:50 pm

    #1254025

    The middle date part I mean the time in the middle on the same date. As I indicated on my example, there are about four the same dates with different time, so I would like to take the duration for only the middle time on the same date. Hope this clarifies.

  • November 22, 2010 at 1:59 pm

    #1254035

    Two questions in one, EJ, just an FYI. 🙂

    First, you need to compare one row to the previous row. This is usually done via a CTE with a ROW_NUMBER() OVER (PARTITION BY <person> ORDER BY <date>), then self joining to find the previous row.

    As to determining the time, what you want is DATEDIFF( mi, PreviousRow.Date, ThisRow.Date). mi is for minutes. The thing is if you want anything but minutes for larger than a day, you'll have to put in some mathmatics to convert that for you. I wouldn't recommend doing that unless you're planning on pushing it to a display, and if so, doing it at the display so you can put in characters like 'hours', 'days', etc, to explain the number.

    If I get a chance later I'll see if I can work out the full query, but that will work to get you started I believe. There's plenty of posts here with solution examples. Just search on 'Row_Number previous row calculation'. Should be a large volume of examples to use.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 22, 2010 at 2:05 pm

    #1254044

    So, a given date will only have either 2 entries or 4 entries, never 3 or 5 or 6?

    Is that correct?

    If so, you could use Row_Number() in a CTE to rank the dates for each day, and then use datediff between the one with a 2 and the one with a 3 for each date that has one.

    IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

    DROP TABLE #T;

    CREATE TABLE #T (

    ID INT IDENTITY PRIMARY KEY,

    PersonID INT,

    MyDateColumn DATETIME);

    INSERT INTO #T (PersonID, MyDateColumn)

    SELECT 1, '1/1/2000 1 pm' UNION ALL

    SELECT 1, '1/1/2000 2 pm' UNION ALL

    SELECT 1, '1/1/2000 3 pm'UNION ALL

    SELECT 1, '1/1/2000 4 pm';

    ;WITH CTE (Row, DateDay, PersonID, MyDateColumn) AS

    (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID, DATEADD(DAY, DATEDIFF(DAY, 0, MyDateColumn), 0) ORDER BY MyDateColumn),

    DATEADD(DAY, DATEDIFF(DAY, 0, MyDateColumn), 0), PersonID, MyDateColumn

    FROM #T)

    SELECT DATEDIFF(MINUTE, CTEa.MyDateColumn, CTEb.MyDateColumn)

    FROM CTE AS CTEa

    INNER JOIN CTE AS CTEb

    ON CTEa.PersonID = CTEb.PersonID

    AND CTEa.DateDay = CTEb.DateDay

    AND CTEa.Row = 2 AND CTEb.Row = 3;

    Of course, that won't work if you have more ways of splitting up a day, unless you get more clever about using the row numbers.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 23, 2010 at 7:05 am

    #1254435

    I got you... Thank you for your help!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply