CREATE TABLE T1
(Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME)
;
INSERT INTO T1
(Asset_Id, Trailer_Id, AssignStart, AssignEnd)
VALUES
(37124, 32607, '2018-04-19 08:55:00', '2018-05-05 10:00:00.000'),
(37124, 32607, '2018-05-05 11:23:00', NULL),
(33000, 30000, '2018-04-01 15:00:00', '2018-04-15 10:30:00.000'),
(34000, 31000, '2018-04-05 10:00:00', '2018-04-10 09:30:00.000'),
(34000, 32500, '2018-04-10 09:31:00', NULL),
(37000, 32600, '2018-04-19 08:55:00', '2018-04-25 08:30:00.000'),
(37000, 32600, '2018-04-25 09:23:00', '2018-04-25 10:00:00.000'),
(37000, 32600, '2018-04-25 11:23:00', '2018-04-30 15:00:00.000'),
(37000, 32600, '2018-04-30 16:15:00', '2018-04-30 17:30:00.000'),
(37000, 32600, '2018-05-01 18:23:00', NULL),
(38000, 36000, '2018-05-01 10:00:00', '2018-05-10 06:30:00.000'),
(38000, 36000, '2018-05-15 09:00:00', '2018-05-20 11:00:00.000'),
(38000, 36000, '2018-05-20 12:00:00', NULL),
(33000, 30000, '2018-05-01 10:00:00', NULL)
;
I have the following sample data -
Asset_Id Trailer_Id AssignStart AssignEnd
37124 32607 2018-04-19 08:55:00.000 2018-05-05 10:00:00.000
37124 32607 2018-05-05 11:23:00.000 NULL
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000 31000 2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000 32500 2018-04-10 09:31:00.000 NULL
37000 32600 2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000 32600 2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000 32600 2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000 32600 2018-04-30 16:15:00 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00 NULL
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000 36000 2018-05-20 12:00:00.000 NULL
33000 30000 2018-05-01 10:00:00.000 NULL
As you can see some of the assignments between Assets and Trailers are ended and started again on the same day - except for last row - Gaps and Islands
Example (1)
Asset_Id Trailer_Id AssignStart AssignEnd
37000 32600 2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000 32600 2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000 32600 2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000 32600 2018-04-30 16:15:00.000 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00.000 NULL
Output for this sample I am expecting is
Asset_Id Trailer_Id AssignStart AssignEnd
37000 32600 2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00.000 NULL
Now this is another section with the Gaps and Islands. Some of assignments between the same assets and trailers are ended and then started again on a future date
Example (2)
Asset_Id Trailer_Id AssignStart AssignEnd
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000 30000 2018-05-01 10:00:00.000 NULL
Output for this sample I am expecting is
Asset_Id Trailer_Id AssignStart AssignEnd
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000 30000 2018-05-01 10:00:00.000 NULL
Example (3)
Asset_Id Trailer_Id AssignStart AssignEnd
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000 36000 2018-05-20 12:00:00.000 NULL
Output for this sample I am expecting is
Asset_Id Trailer_Id AssignStart AssignEnd
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 NULL
I struggling to write a query that will provide the following output
Asset_Id Trailer_Id AssignStart AssignEnd
37124 32607 2018-04-19 08:55:00.000 NULL
33000 30000 2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000 31000 2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000 32500 2018-04-10 09:31:00.000 NULL
37000 32600 2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000 32600 2018-05-01 18:23:00.000 NULL
38000 36000 2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000 36000 2018-05-15 09:00:00.000 NULL
33000 30000 2018-05-01 10:00:00.000 NULL
Using the answer from this SO answer as a base this is my attempt, it's not quite right